import time

from django.shortcuts import render

# Create your views here.
from django.db.models import Q
from django.http import HttpResponse, JsonResponse
from django.utils.encoding import iri_to_uri

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Protection
from openpyxl.styles.borders import Border, Side
from openpyxl.worksheet.datavalidation import DataValidation
import io
import pandas as pd
# import requests
import json
import pymysql
from datetime import datetime
import traceback
import re

from apps.homepage.models import ZtStory,ZtModule, ZtProject, ZtProjectProduct, ZtTeam, ZtProductPlan, ZtTask, ZtTaskTeam
from zentao.settings import PYMYSQL_CONF

import time
from functools import wraps

def timing_decorator(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        print(f"Function {func.__name__!r} executed in {(end_time - start_time)*1000:.2f}ms")
        return result
    return wrapper

#判断是否登录
# class TokenAuthPermission(BasePermission):
def has_permission(request):

    # response = Response({'code': 0,  'message': '未登录'}, status=status.HTTP_401_UNAUTHORIZED)

    # print(request.session.get('token'), request.META.get('HTTP_TOKEN'))

    # session没有token，则未登录
    session_token = request.session.get('token')
    if not session_token:
        # print('没有session')
        return False

    # 如果未传入token，则未登录
    request_token = request.META.get('HTTP_TOKEN')
    if not request_token:
        # print('没有token')
        return False

    #如果都传入，则比对是否一致
    if session_token == request_token:
        return True
    else:
        return False

#查询一个产品是否对应多个项目，如果是需要筛选模块（一个项目对应多个产品，默认这个项目包含所有产品的模块）
def if_select_module(project_id):
    #查询所有项目
    projects = ZtProject.objects.filter(project=0, deleted='0').values('id').distinct()
    projects = [project['id'] for project in projects]

    #查询所有项目与产品的对应表
    product_projectSet_dict = {}  # 产品对应的项目字典
    project_productNum = set()  #项目对应的产品集合

    project_products = ZtProjectProduct.objects.all().values('project', 'product').distinct()

    #计算指定项目对应的产品的项目数量
    for project_product in project_products:
        product = project_product['product']
        project = project_product['project']

        # 项目对应的产品集合
        if project == project_id:
            project_productNum.add(product)

        #产品对应的项目集合
        if product not in product_projectSet_dict.keys():
            product_projectSet_dict[product] = set()
        if project in projects:
            product_projectSet_dict[product].add(project)

    #如果项目没有对应的产品（实际不可能存在）或者没有该项目
    if len(project_productNum) == 0:
        return False
    elif len(project_productNum) == 1:
        product_projectSet = product_projectSet_dict[list(project_productNum)[0]]
        #如果产品只有一个项目，也不需要筛选
        if len(product_projectSet) == 1:
            return False
        else:
            return True
    else:
        #如果一个项目对应多个产品，不筛选模块
        return False

# # 将路径整理成树结构的函数
def module_to_tree(modules):

    # 初始化树结构
    tree = {}

    #模块id和名称对应
    id2name = {}

    id2pathName = {}

    # 遍历路径列表，构建树结构
    for module in modules:
        id2name[str(module['id'])] = module['name']
        # print([module['id'],  module['name']])

        # 移除路径两端的逗号，并分割成步骤列表
        steps = module['path'].strip(',').split(',')

        # 递归地构建或更新树结构
        current_level = tree
        full_module_name = ''  # 用于记录完整的模块名，包含父节点模块名

        for step in steps:
            if step:  # 确保步骤不为空

                # 构建完整的模块名，通过父节点模块名和当前节点名
                if full_module_name:
                    full_module_name += '/' + id2name[step]
                else:
                    full_module_name = id2name[step]

                if step not in current_level:
                    id2pathName[module['id']] = "{}: {}".format(module['id'], full_module_name)
                    current_level[step] = {'id':'module-{}'.format(module['id']),
                                           'name': "{}: {}".format(module['id'], full_module_name),
                                           'child': {}}

                current_level = current_level[step]['child']
    return tree, id2pathName

# 删除模块的child为空的情况
def remove_empty_child_modules_node(data):
    # If the data itself is empty, return None
    if not data:
        return None
    # with open('1.json','w') as f:
    #     json.dump(data,f,ensure_ascii=False)
    # Check if the current node is a module
    if data['type'] == 'module':
        # Recursively remove empty modules from children
        data['child'] = [remove_empty_child_modules_node(child) for child in data['child']]
        # Filter out None values from the list after recursion
        data['child'] = [child for child in data['child'] if child is not None]
        # If after removing children, the module has no children left, remove it
        if not data['child']:
            return None
    return data

def remove_empty_child_modules(data):
    # Process each item in the list
    return [remove_empty_child_modules_node(item) for item in data if
            remove_empty_child_modules_node(item) is not None]

#提取树结构所有节点的名字
def extract_names(data):
    names = []

    def recurse(node):
        # 将当前节点的name添加到列表中

        names.append(node['name'])
        # 如果当前节点有子节点，递归地处理每个子节点
        if 'child' in node and node['child']:
            for child in node['child'].values():
                recurse(child)

    # 遍历顶层节点
    for key, value in data.items():
        recurse(value)

    return names

def get_modules(project_id):
    # 获取该项目所有模块
    query_module = Q()
    query_module &= Q(root__projectProduct_product__project_id=project_id)  # 根据项目id查询模块
    query_module &= Q(root__projectProduct_product__project__deleted='0')  # 项目不能被删除
    query_module &= Q(deleted='0')  # 模块不能被删除

    modules = ZtModule.objects.filter(query_module).values('id', 'name', 'path').distinct()

    # 处理模块将,151,152,改为,738,151,152,
    # 获取所有的位尾节点模块
    model_path_end = {}
    for module in modules:
        # print(module['id'],module['name'],module['path'])
        path = module['path'].strip(',').split(',')
        if len(path) > 1:
            model_path_end[path[-1]] = {'id': module['id'], 'path': module['path']}

    # 处理首节点对应尾巴节点的情况
    for i, module in enumerate(modules):
        path = module['path'].strip(',').split(',')
        if len(path) > 1 and path[0] in model_path_end.keys():
            pre_path = model_path_end[path[0]]
            path = pre_path['path'] + ''.join(path[1:]) + ','
            module['path'] = path

    # 排序，将父模块放到前面
    modules = sorted(modules, key=lambda x: len(x['path'].split(',')))

    # 整理模块树结构
    module_tree, id2pathName = module_to_tree(modules)

    # 如果该项目对应的产品有多个项目，则需要筛选模块
    # if if_select_module(int(project_id)):
        # 筛选有需求的模块
        # st = time.time()
        # module_tree = remove_empty_child_modules(module_tree)

    model_names = extract_names(module_tree)

    return model_names,id2pathName

#获取需求列表
def get_stories(project_id, id2pathName):
    name_map = {}

    #项目对应的产品的需求
    query = Q()
    query &= Q(product__projectProduct_product__project_id=project_id)  # 根据项目id查询需求
    query &= Q(product__projectProduct_product__project__deleted='0')  # 项目不能被删除
    query &= Q(deleted='0')  # 需求不能被删除
    query &= Q(type='story')  # 确保为研发需求

    product_stories = ZtStory.objects.filter(query).values('id', 'title').distinct()
    for product_story in product_stories:
        name_map[product_story['id']] = product_story['title']

    # 项目对应的需求
    query = Q()
    query &= Q(projectStory_story__project__id=project_id)  # 根据项目id查询需求
    query &= Q(projectStory_story__project__deleted='0')  # 项目不能被删除
    query &= Q(deleted='0')  # 需求不能被删除
    query &= Q(type='story')  # 确保为研发需求

    stories = ZtStory.objects.filter(query).values('id', 'title', 'parent', 'module','plan','status').distinct()

    # 排序，将父模块放到前面
    stories = sorted(stories, key=lambda x: x['parent'])

    #需求模块映射
    story2module = {}
    story_id2name = {}
    parent_stories = []
    story_ids = [0]

    for story in stories:
        # print(story['title'])
        # 模块id
        if story['module'] in id2pathName.keys():
            module_name = id2pathName[story['module']]
            # story_name = "{}-{}".format(id2pathName[story['module']],story['title'])
        else:
            # module_name = '0: 无模块'
            module_name = ''
            # story_name = "0-{}".format(story['title'])

        if story['parent'] != 0 and story['parent'] != -1:
            story_name = "{}: {}/{}".format(story['id'], name_map[story['parent']], story['title'])
        else:
            story_name = "{}: {}".format(story['id'], story['title'])
            # 当需求对应的计划为空,并且状态为评审通过（即激活），才可以当父需求
            if story['plan'] == '' and story['status'] == 'active':
                parent_stories.append(story_name)
                story_ids.append(story['id'])

        #只有无子需求的需求和子需求,并且状态为评审通过（即激活）才可选关联任务
        if story['parent'] != -1 and story['status'] == 'active':
            story2module[story_name] = module_name

        story_id2name[story['id']] = story_name

    return story2module, story_id2name, parent_stories, max(story_ids)+1

#获取需求树结构
def get_story_tree(project_id):
    # 项目对应的需求
    query = Q()
    query &= Q(projectStory_story__project__id=project_id)  # 根据项目id查询需求
    query &= Q(projectStory_story__project__deleted='0')  # 项目不能被删除
    query &= Q(deleted='0')  # 需求不能被删除
    query &= Q(type='story')  # 确保为研发需求

    stories = ZtStory.objects.filter(query).values('id', 'title', 'parent', 'status', 'product', 'plan').distinct()

    # 排序，将父模块放到前面
    stories = sorted(stories, key=lambda x: x['parent'])

    story_ids = [0]
    story_tree = {}
    id2name = {}

    for story in stories:

        story_name = "{}: {}".format(story['id'], story['title'])
        id2name[story['id']] = story_name

        #只有父节点
        if story['parent'] == 0 or story['parent'] == -1:
            # 当需求对应的计划为空,并且状态为评审通过（即激活），才可以当父需求
            if story['plan'] == '' and story['status'] == 'active':
                story_ids.append(story['id'])

            #树结构
            story_tree[story_name] = {"id":story['id'], 'parent': story['parent'], "title":story['title'], 'status':story['status'], 'child':{}}

        else:

            if story['parent'] not in story_ids:
                # 树结构
                story_tree[story_name] = {"id": story['id'], 'parent': story['parent'],"title": story['title'], 'status':story['status'], 'child': {}}
            else:
                story_tree[id2name[story['parent']]]['child'][story_name] = {"id": story['id'], 'parent': story['parent'], "title": story['title'], 'status':story['status'], 'child': {}}

    return max(story_ids)+1,story_tree

#获取计划列表
def get_plans(project_id):
    # 获取该项目所有模块
    query_module = Q()
    query_module &= Q(product__projectProduct_product__project_id=project_id)  # 根据项目id查询模块
    query_module &= Q(product__projectProduct_product__project__deleted='0')  # 项目不能被删除
    query_module &= Q(deleted='0')  # 模块不能被删除

    plans = ZtProductPlan.objects.filter(query_module).values('id', 'parent', 'title').distinct()

    # 排序，将父模块放到前面
    plans = sorted(plans, key=lambda x: x['parent'])

    plan_names = []
    name_map = {}
    # modules = id2pathName.keys()

    for plan in plans:

        name_map[plan['id']] = plan['title']

        if plan['parent'] != 0 and plan['parent'] != -1:
            plan_name = "{}: {}/{}".format(plan['id'], name_map[plan['parent']], plan['title'])
        elif plan['parent'] == 0:
            plan_name = "{}: {}".format(plan['id'], plan['title'])
        else:
            continue

        plan_names.append(plan_name)

    return plan_names

#获取任务列表
def get_tasks(project_id):
    # 初始化一个空的查询集
    query = Q()
    query &= Q(project__id=project_id)  # 根据项目id查询需求
    query &= Q(project__deleted='0')  # 项目不能被删除
    query &= Q(deleted='0')  # 任务不能被删除

    tasks = ZtTask.objects.filter(query).values('id','name','parent', 'story').distinct()

    # 排序，将父模块放到前面
    tasks = sorted(tasks, key=lambda x: x['parent'])

    task2story = {}
    task_ids = [0]
    task_tree = {}
    id2name = {}

    for task in tasks:

        task_name = "{}: {}".format(task['id'], task['name'])
        id2name[task['id']] = task_name

        #只有父节点
        if task['parent'] == 0 or task['parent'] == -1:
            task_ids.append(task['id'])

            # if task['story'] == 0:
            #     story_name = ""
            # else:
            #     story_name = story_id2name[task['story']]

            task2story[task_name] = ''

            #树结构
            task_tree[task_name] = {"id":task['id'], 'parent': task['parent'], "name":task['name'], 'child':{}}

        else:
            if task['parent'] not in task_ids:
                # 树结构
                task_tree[task_name] = {"id": task['id'], 'parent': task['parent'],"name": task['name'], 'child': {}}
            else:
                task_tree[id2name[task['parent']]]['child'][task_name] = {"id": task['id'], 'parent': task['parent'], "name": task['name'], 'child': {}}

    return task2story, max(task_ids)+1, task_tree

#获取团队列表
def get_team(project_id):
    # 搜索条件
    # 初始化一个空的查询集
    query = Q()
    query &= Q(root__id=project_id)  # 根据项目id查询需求
    query &= Q(root__deleted='0')  # 项目不能被删除

    team = ZtTeam.objects.filter(query).select_related('account').distinct()

    persons = []

    for team_account in team:
        # 获取用户名和真实姓名
        account = team_account.account
        persons.append("{}: {}".format(account.account, account.realname))

    return persons

#获取项目执行
def get_execution(project_id):

    # 查询条件
    query = Q()
    query &= Q(project=project_id)  # 对应的执行版本
    query &= Q(deleted='0')  # 执行不能被删除

    executions = ZtProject.objects.filter(query).values('id', 'name').distinct()

    execution_names = []
    for execution in executions:
        execution_names.append('{}: {}'.format(execution['id'], execution['name']))

    return execution_names

def get_task_estimate(login_account):
    # 第一步：从ZtTaskTeam中获取account等于leilimei的任务ID
    task_ids_in_team = ZtTaskTeam.objects.filter(account__account=login_account).values_list('task', flat=True)

    # 第二步：在ZtTask中根据这些任务ID以及assignedTo进行筛选
    query = Q(id__in=task_ids_in_team) | Q(assignedTo__account=login_account) | Q(finishedBy=login_account)
    query &= Q(deleted='0')
    tasks = ZtTask.objects.filter(query).exclude(status='closed').select_related('assignedTo', 'module', 'finishedBy') \
                .prefetch_related('taskteam_task', 'taskteam_task__account').distinct()

    #该账户的任务
    account_tasks = {}
    parent_id = set()

    for task in tasks:

        # print(task.id)

        #父节点不参与工时填写
        if task.parent == -1:
            continue

        #筛选父节点
        if task.parent != 0:
            parent_id.add(task.parent)

        # 如果是多人任务则拼接，否则使用指派人
        team_list = task.taskteam_task.all()  # 获取任务团队

        if team_list:

            for person in team_list:
                if person.account.account == login_account:
                    realname = person.account.realname
                    estimate = float(person.estimate)
                    consumed = float(person.consumed)
                    left = float(person.left)

                    account_tasks[task.id]={
                        'realname': f'{person.account.account}: {realname}',
                        'estimate': estimate,
                        'consumed': consumed,
                        'left': left,
                        'task': task.name,
                        'parent': task.parent
                    }

        else:

            # 获取用户名和真实姓名
            assignedTo = task.assignedTo
            finishedBy = task.finishedBy

            #如果有完成人，则用完成人替代
            if finishedBy:
                assignedTo = finishedBy

            if assignedTo and assignedTo.account==login_account:
                realname = assignedTo.realname
                estimate = task.estimate
                consumed = task.consumed
                left = task.left

                account_tasks[task.id]={
                    'realname': f'{assignedTo.account}: {realname}',
                    'estimate': estimate,
                    'consumed': consumed,
                    'left': left,
                    'task': task.name,
                    'parent': task.parent
                }
    #筛选父需求
    # 第二步：在ZtTask中根据这些任务ID以及assignedTo进行筛选
    query = Q(id__in=list(parent_id))
    query &= Q(deleted='0')
    tasks = ZtTask.objects.filter(query).distinct()
    name_map = {}
    for task in tasks:
        name_map[task.id] = task.name

    for key,value in account_tasks.items():
        if value['parent'] in name_map.keys():
            value['task'] = f'{key}: {name_map[value["parent"]]}/{value["task"]}'
        else:
            value['task'] = f'{key}: {value["task"]}'
        account_tasks[key]=value

    # name_map[assignedTo_account] = assignedTo_realname
    return list(account_tasks.values())

def set_rule(dv, col_name, ws, list_length):

    # 将数据验证对象应用于单元格范围
    ws.add_data_validation(dv)

    # for i in range(list_length):
    #     dv.add(ws["{}{}".format(col_name, i + 3)])
    dv.add('{}{}:{}{}'.format(col_name,3,col_name,3 + list_length))

    return ws

#生成任务的模板
@timing_decorator
def task_template(request):

    # # 检查用户是否登录
    if not has_permission(request):
        # 如果用户未登录，抛出 AuthenticationFailed 异常
        return JsonResponse({'code': 0, 'msg': '未登录'},status=401)

    if request.method == 'GET':
        # print(request.session)
        # if 'token' in request.session:
        #     del request.session['token']
        # 获取参数
        project_id = request.GET.get('project_id')
        task_type = request.GET.get('task_type')

        # 如果project_id没有传
        if not project_id:
            return JsonResponse({'code': 0, 'msg': '没有携带项目id'},status=400)

        model_names, id2pathName = get_modules(project_id)
        story2module, story_id2name, _, _ = get_stories(project_id, id2pathName)
        perosns_list = get_team(project_id)

        # print(model_names)
        # print(story2module)

        # 创建一个新的 Excel 工作簿和工作表
        wb = Workbook()
        ws = wb.active

        #设置列宽
        ws.column_dimensions['A'].width = 25
        ws.column_dimensions['B'].width = 30
        ws.column_dimensions['C'].width = 35
        ws.column_dimensions['D'].width = 30
        ws.column_dimensions['E'].width = 30
        ws.column_dimensions['F'].width = 30
        ws.column_dimensions['G'].width = 20
        ws.column_dimensions['H'].width = 20
        ws.column_dimensions['I'].width = 30
        ws.column_dimensions['J'].width = 30
        ws.column_dimensions['K'].width = 30
        ws.column_dimensions['L'].width = 25
        ws.column_dimensions['M'].width = 23
        ws.column_dimensions['N'].width = 23
        ws.column_dimensions['O'].width = 30
        ws.column_dimensions['P'].width = 30
        ws.column_dimensions['Q'].width = 23
        ws.column_dimensions['R'].width = 20

        #设置第一行行宽度
        ws.row_dimensions[1].height = 50

        # 在第一行写入标题，并设置为加粗
        titles = ['迭代任务名称（必填）','研发需求', '功能模块\n（如选择了研发需求，则自动带出功能模块，否则手动选择）',
                  '父任务\n(默认从系统导出，否则从D列填写)','任务名称(必填)','任务描述（必填）','任务类型（必填）','难度系数',
                  '负责人（必填）','创建人','所属人','预估工作量（必填）\n（例：8）','计划开始时间（必填）\n（例：2024-09-09）',
                  '计划截止日期（必填）\n（例：2024-09-09）','实际开始日期\n（例：\n2024-09-09 05:02:22）',
                  '实际结束日期\n（例：\n2024-09-09 05:02:22）','优先级\n（1为最高优先级别）',  '状态（必填）']
        for col_num, title in enumerate(titles, start=1):
            cell = ws.cell(row=1, column=col_num, value=title)
            cell.font = Font(bold=True)
            cell.alignment = Alignment(wrapText=True,horizontal='center',vertical='center')
            if col_num in [1,5,6,7,9,12,13,14,18]:
                #必填为黄色
                cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            else:
                cell.fill = PatternFill(start_color='A0A0A0', end_color='A0A0A0', fill_type='solid')

        #第二行为示例
        examples = ['参考样例请勿删除：XXX模块迭代任务','业务流程清单', '用户管理', '业务流程清单', '业务流程清单', '实现业务流程清单功能',
                    '开发', '一般','zhangsan: 张三', 'zhangsan: 张三', 'zhangsan: 张三', '20', '2024-09-09','2024-09-09',
                    '2024-09-09 05:02:22','2024-09-09 05:02:22', '1', '已完成']
        for col_num, text in enumerate(examples, start=1):
            cell = ws.cell(row=2, column=col_num, value=text)
            cell.font = Font(color='FF0000')

        thin_border = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))
        area = ws['$A$1:$R$50']
        for k in area:
            for h in k:
                h.border = thin_border

        # 定义需要下拉框的长度
        list_length = 1000

        #对应关系表
        sheet2_name = "中间表"
        ws_middle = wb.create_sheet(title=sheet2_name)
        story_num = 0
        module_num = 0
        module_list = []

        #写入需求和模块对应关系
        for row_num, key in enumerate(story2module.keys(), start=1):
            story_name = ': '.join(key.split(': ')[1:])
            ws_middle.cell(row=row_num, column=1, value=story_name)
            ws_middle.cell(row=row_num, column=7, value=key)

            module = story2module[key]
            module_name = ': '.join(module.split(': ')[1:])
            ws_middle.cell(row=row_num, column=2, value=module_name)
            # ws_middle.cell(row=row_num, column=8, value=module)

            if module_name not in module_list:
                module_list.append(module_name)
            story_num+=1

        # 写入模块列表
        for row_num, module in enumerate(model_names, start=1):
            if module == '':
                continue
            module_name = ': '.join(module.split(': ')[1:])
            ws_middle.cell(row=row_num, column=8, value=module_name)
            ws_middle.cell(row=row_num, column=9, value=module)

            module_num += 1

        # 写入迭代列表
        execution_num = 0
        execution_names = get_execution(project_id)
        for row_num, execution in enumerate(execution_names, start=1):
            execution_name = ': '.join(execution.split(': ')[1:])
            ws_middle.cell(row=row_num, column=10, value=execution_name)
            ws_middle.cell(row=row_num, column=11, value=execution)

            execution_num += 1

        #写入父任务
        task_num = 0
        task2story, _id, _ = get_tasks(project_id)
        # 写入需求和模块对应关系
        for row_num, key in enumerate(task2story.keys(), start=1):
            ws_middle.cell(row=row_num, column=3, value=key)
            # ws_story2module.cell(row=row_num, column=4, value=task2story[key])
            task_num+=1

        # 写入指派人
        person_num = 0
        # 写入需求和模块对应关系
        for row_num, person in enumerate(perosns_list, start=1):
            ws_middle.cell(row=row_num, column=13, value=person)
            # ws_story2module.cell(row=row_num, column=4, value=task2story[key])
            person_num += 1

        #新增任务添加
        sheet1_name = "Sheet"
        # 写入需求和模块对应关系
        for i in range(list_length):
            # ws_middle["$C${}".format(task_num + i+1)] = \
            #     '=IF(AND({}!$E${}<>"", {}!$D${}=""), "{}: "&{}!$E${}, "")'.format(sheet1_name,i + 3,sheet1_name,i + 3, _id+i,sheet1_name,i + 3)
            ws_middle["$C${}".format(task_num + i + 1)] = \
                '=IF(AND(INDIRECT("{}!$E${}")<>"", INDIRECT("{}!$D${}")=""), "{}: "&INDIRECT("{}!$E${}"), "")'.format(sheet1_name, i + 3, sheet1_name,
                                                                                  i + 3, _id + i, sheet1_name, i + 3)

        for i in range(task_num+list_length):

            #提取任务的id
            ws_middle["$D${}".format(i + 1)] = \
                '=IF($C${}<>"", VALUE(LEFT($C${},FIND(": ",$C${})-1)), VALUE(10000))'.format(i+1,i+1,i+1)

            #对任务id进行排序
            ws_middle["E{}".format(i + 1)] = \
                '=RANK($D${},$D$1:$D${},1)+(COUNTIF($D$1:$D${},$D${})-1)'.format(i+1,task_num+list_length,i+1,i+1)

            # #根据排名重写任务
            # for i in range(task_num+list_length):
            #     ws_middle["$F${}".format(i + 1)] = \
            #         '=IF($C${}<>"", RIGHT(INDEX($C:$C,MATCH({},$E:$E,0)),LEN(INDEX($C:$C,MATCH({},$E:$E,0)))-1-FIND(": ",INDEX($C:$C,MATCH({},$E:$E,0)))), "")'.format(i + 1,i + 1,i + 1,i + 1)
            # 根据排名重写任务
            # for i in range(task_num + list_length):
            ws_middle["$F${}".format(i + 1)] = \
                '=INDEX($C:$C,MATCH({},$E:$E,0))'.format(i + 1)

            #分理出名称
            # for i in range(task_num + list_length):
            ws_middle["$L${}".format(i + 1)] = \
                '=IF($F${}<>"", RIGHT($F${}, LEN($F${}) - 1 - FIND(": ", $F${})), "")'.format(i + 1,i + 1,i + 1,i + 1)

        # 隐藏中间表
        ws_middle.sheet_state = 'hidden'

        # 启用工作表保护
        ws_middle.protection.sheet = True
        # 设置密码（可选）
        # 如果设置了密码，用户需要输入密码才能取消保护
        ws_middle.protection.password = 'zentao_export_template'

        # 设置哪些操作是被允许的，以下为默认设置，即不允许任何操作
        ws_middle.protection.enable()

        # 冻结前两行
        ws.freeze_panes = 'A3'

        #执行下拉列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$J$1:$J${execution_num}', allow_blank=True)
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(story_list)), allow_blank=True)
        ws = set_rule(dv, 'A', ws, list_length)

        #需求下拉列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$A$1:$A${story_num}', allow_blank=True)
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(story_list)), allow_blank=True)
        ws = set_rule(dv, 'B', ws, list_length)

        # 模块下拉列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$H$1:$H${module_num}', allow_blank=True)
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(story_list)), allow_blank=True)
        ws = set_rule(dv, 'C', ws, list_length)

        #父任务下拉列表
        formula = '=OFFSET({}!$L$1,,,SUMPRODUCT(N(LEN({}!$L:$L)>0)),)'.format(sheet2_name,sheet2_name)
        dv = DataValidation(type="list", formula1=formula, allow_blank=True)
        ws = set_rule(dv, 'D', ws, list_length)

        # 指派给列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$M$1:$M${person_num}', allow_blank=True)
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(story_list)), allow_blank=True)
        ws = set_rule(dv, 'I', ws, list_length)

        # 创建人列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$M$1:$M${person_num}', allow_blank=True)
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(story_list)), allow_blank=True)
        ws = set_rule(dv, 'J', ws, list_length)

        # 所属人列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$M$1:$M${person_num}', allow_blank=True)
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(story_list)), allow_blank=True)
        ws = set_rule(dv, 'K', ws, list_length)

        #需求列填写，设置第一列的值根据映射关系以及第二列自动填写
        for i in range(list_length):
            ws["$C${}".format(i + 3)] = '=IF($B${}<>"", INDEX({}!$B:$B,MATCH($B${},{}!$A:$A,0)), "")'.format(i+3,sheet2_name,i+3,sheet2_name)

        # 预计只能输入数字，范围为0-8
        dv = DataValidation(type="whole", formula1=0,formula2=1, allow_blank=True)
        ws = set_rule(dv, 'L', ws, list_length)

        # 预计开始、截止日期为纯文本格式
        for i in range(list_length):
            for j in range(1,19):
                ws.cell(row=i+2, column=j).number_format = '@'

        # 状态列表
        status_list = ['未开始', '进行中', '已完成', '已关闭', '已暂停', '已取消']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(status_list)), allow_blank=True)
        ws = set_rule(dv, 'R', ws, list_length)

        #任务类型列表
        if task_type == 'task':
            type_list = ['设计','开发','需求','测试','研究','讨论','界面','事务','运维','告警','请求','咨询','报障','其他']
        elif task_type == 'implement':
            type_list = ['实施']
        else:
            type_list = []
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(type_list)), allow_blank=True)
        ws = set_rule(dv, 'G', ws, list_length)

        #难度系数列表
        difficuty_list = ['非常重要','重要','一般','不重要']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(difficuty_list)), allow_blank=True)
        ws = set_rule(dv, 'H', ws, list_length)

        #优先级列表
        pri_list = ['1','2','3','4']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(pri_list)), allow_blank=True)
        ws = set_rule(dv, 'Q', ws, list_length)

        # 使用 io.BytesIO() 创建一个内存中的文件
        buffer = io.BytesIO()
        # 将工作簿保存到内存中的文件
        wb.save(buffer)
        # 将文件指针移到开始位置
        buffer.seek(0)

        # 设置文件名为中文，并进行URL编码
        filename = "任务导入模板.xlsx"
        encoded_filename = iri_to_uri(filename)

        # 创建一个HttpResponse对象，设置Content-Type和Content-Disposition
        response = HttpResponse(
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            content=buffer.getvalue()
        )
        response['Content-Disposition'] = f'attachment; filename="{encoded_filename}"'

        # 关闭buffer
        buffer.close()

        return response
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#生成任务的模板
@timing_decorator
def task_estimate_template(request):

    # 检查用户是否登录
    if not has_permission(request):
        # 如果用户未登录，抛出 AuthenticationFailed 异常
        return JsonResponse({'code': 0, 'msg': '未登录'},status=401)

    if request.method == 'GET':
        # print(request.session)
        # if 'token' in request.session:
        #     del request.session['token']
        #当前用户
        login_account = request.session['account']
        # login_account = request.GET.get('login_account')
        # if not login_account:
        #     return JsonResponse({'code':0, 'msg':'未选择用户'})

        #获取该账户下的任务
        tasks = get_task_estimate(login_account)

        #如果任务为0，则返回没有任务
        if len(tasks) == 0:
            res = {
                'code':0,
                'msg': '没有相关任务'
            }
            return JsonResponse(res)

        # print(model_names)
        # print(story2module)

        # 创建一个新的 Excel 工作簿和工作表
        wb = Workbook()
        ws = wb.active

        #设置列宽
        ws.column_dimensions['A'].width = 25
        ws.column_dimensions['B'].width = 30
        ws.column_dimensions['C'].width = 35
        ws.column_dimensions['D'].width = 30
        ws.column_dimensions['E'].width = 30
        ws.column_dimensions['F'].width = 30
        ws.column_dimensions['G'].width = 20
        ws.column_dimensions['H'].width = 20

        #设置第一行行宽度
        ws.row_dimensions[1].height = 50

        # 在第一行写入标题，并设置为加粗
        titles = ['用户（必填）','任务名称（必填）', '预计工时\n（模板自动计算，请勿修改）',
                  '已消费工时\n（模板自动计算，请勿修改）','剩余工时\n（模板自动计算，请勿修改）',
                  '工时日期（必填）','工作内容（必填）','消耗工时（必填）']
        for col_num, title in enumerate(titles, start=1):
            cell = ws.cell(row=1, column=col_num, value=title)
            cell.font = Font(bold=True)
            cell.alignment = Alignment(wrapText=True,horizontal='center',vertical='center')
            if col_num in [1,2,6,7,8]:
                #必填为黄色
                cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            elif col_num in [3,4,5]:
                # 不可修改为蓝色
                cell.fill = PatternFill(start_color='0000FF', end_color='0000FF', fill_type='solid')
                cell.font = Font(color='FFFF00',bold=True)#黄色字体
            else:
                cell.fill = PatternFill(start_color='A0A0A0', end_color='A0A0A0', fill_type='solid')

        #第二行为示例
        examples = ['参考样例请勿删除：zhangsan: 张三','样例：任务1', 20, 5, 15, '2020-09-06','工作内容干了什么',8]
        for col_num, text in enumerate(examples, start=1):
            cell = ws.cell(row=2, column=col_num, value=text)
            cell.font = Font(color='FF0000')

        thin_border = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))
        area = ws['$A$1:$H$50']
        for k in area:
            for h in k:
                h.border = thin_border

        # 定义需要下拉框的长度
        list_length = 1000

        #对应关系表
        sheet2_name = "中间表"
        ws_middle = wb.create_sheet(title=sheet2_name)

        account_person = tasks[0]['realname']

        #写入任务相关详情
        task_num = 0
        for row_num, task in enumerate(tasks, start=1):
            task_name = ': '.join(task['task'].split(': ')[1:])
            ws_middle.cell(row=row_num, column=1, value=task_name)
            ws_middle.cell(row=row_num, column=2, value=task['task'])
            ws_middle.cell(row=row_num, column=3, value=task['estimate'])
            ws_middle.cell(row=row_num, column=4, value=task['consumed'])
            ws_middle.cell(row=row_num, column=5, value=task['left'])
            task_num += 1

        # 写入指派人
        person_num = 0
        perosns_list = [account_person]
        # 写入需求和模块对应关系
        for row_num, person in enumerate(perosns_list, start=1):
            ws_middle.cell(row=row_num, column=6, value=person)
            # ws_story2module.cell(row=row_num, column=4, value=task2story[key])
            person_num += 1

        # 隐藏中间表
        ws_middle.sheet_state = 'hidden'

        # 启用工作表保护
        ws_middle.protection.sheet = True
        # 设置密码（可选）
        # 如果设置了密码，用户需要输入密码才能取消保护
        ws_middle.protection.password = 'zentao_export_template'

        # 设置哪些操作是被允许的，以下为默认设置，即不允许任何操作
        ws_middle.protection.enable()

        # 冻结前两行
        ws.freeze_panes = 'A3'

        #用户下拉列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$F$1:$F${person_num}', allow_blank=True)
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(story_list)), allow_blank=True)
        ws = set_rule(dv, 'A', ws, list_length)

        #任务下拉列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$A$1:$A${task_num}', allow_blank=True)
        ws = set_rule(dv, 'B', ws, list_length)

        # 指派给列表


        # 总工时填写，设置第一列的值根据映射关系以及第二列自动填写
        for i in range(list_length):
            ws["$C${}".format(i + 3)] = '=IF($B${}<>"", INDEX({}!$C:$C,MATCH($B${},{}!$A:$A,0)), "")'.format(i+3,sheet2_name,i+3,sheet2_name)

            # 消耗填写，设置第一列的值根据映射关系以及第二列自动填写
            ws["$D${}".format(i + 3)] = '=IF($B${}<>"", INDEX({}!$D:$D,MATCH($B${},{}!$A:$A,0))+ SUMIF($B1:$B{},B{},$H1:$H{}), "")'\
                                        .format(i + 3,sheet2_name,i + 3,sheet2_name, i + 2, i + 3, i + 2)
            # ws["$D${}".format(i + 3)] = '=IF($B${}<>"", INDEX({}!$D:$D,MATCH($B${},{}!$A:$A,0))+ SUM(IFERROR(FIND(B{},B1:B{})^0,0)*H1:H{}), "")' \
            #                             .format(i + 3, sheet2_name, i + 3, sheet2_name, i + 3, i + 2, i + 2)

            # 剩余填写，设置第一列的值根据映射关系以及第二列自动填写
            # ws["$E${}".format(i + 3)] = '=IF($B${}<>"", C{}-D{}-SUMIF($B1:$B{},B{},$H1:$H{}), "")'.format(i + 3,i + 3, i + 3, i + 2, i + 3, i + 2)
            ws["$E${}".format(i + 3)] = '=IF($B${}<>"", C{}-D{}, "")'.format(i + 3, i + 3,i + 3)

        # # 预计开始、截止日期为纯文本格式
        for i in range(list_length):
            for j in [1,2,6,7]:
                ws.cell(row=i + 1, column=j).number_format = '@'

        # 保护工作表
        ws.protection.sheet = True
        # 设置允许编辑的列，这里排除 C、D、E 列
        ws.protection.password = 'zentao_export_template'  # 可根据需要设置密码
        columns_to_unlock = ['A', 'B', 'F', 'G', 'H']
        for col in columns_to_unlock:
            for cell in ws[col]:
                cell.protection = Protection(locked=False)

        # 使用 io.BytesIO() 创建一个内存中的文件
        buffer = io.BytesIO()
        # 将工作簿保存到内存中的文件
        wb.save(buffer)
        # 将文件指针移到开始位置
        buffer.seek(0)

        # 设置文件名为中文，并进行URL编码
        filename = "任务工时导入模板.xlsx"
        encoded_filename = iri_to_uri(filename)

        # 创建一个HttpResponse对象，设置Content-Type和Content-Disposition
        response = HttpResponse(
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            content=buffer.getvalue()
        )
        response['Content-Disposition'] = f'attachment; filename="{encoded_filename}"'

        # 关闭buffer
        buffer.close()

        return response
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#生成研发需求的模板
@timing_decorator
def story_template(request):

    # 检查用户是否登录
    if not has_permission(request):
        # 如果用户未登录，抛出 AuthenticationFailed 异常
        return JsonResponse({'code': 0, 'msg': '未登录'},status=401)

    if request.method == 'GET':
        # print(request.session)
        # if 'token' in request.session:
        #     del request.session['token']
        # st1 = time.time()
        # st = time.time()
        # 获取参数
        project_id = request.GET.get('project_id')

        # 如果project_id没有传
        if not project_id:
            return JsonResponse({'code': 0, 'msg': '没有携带项目id'},status=400)

        # print('获取参数耗时：',time.time()-st)

        # st = time.time()
        module_names, id2pathName = get_modules(project_id)
        _, _, parent_stories, _id = get_stories(project_id, id2pathName)
        # print('获取模块和需求耗时：', time.time() - st)
        # plan_names = get_plans(project_id)

        # print(model_names)
        # print(story2module)

        # st = time.time()
        # 创建一个新的 Excel 工作簿和工作表
        wb = Workbook()
        ws = wb.active
        # print('创建excel耗时：', time.time() - st)

        # st = time.time()
        #设置列宽
        ws.column_dimensions['A'].width = 25
        ws.column_dimensions['B'].width = 30
        ws.column_dimensions['C'].width = 30
        ws.column_dimensions['D'].width = 30
        ws.column_dimensions['E'].width = 20
        ws.column_dimensions['F'].width = 20
        ws.column_dimensions['G'].width = 23
        ws.column_dimensions['H'].width = 20
        ws.column_dimensions['I'].width = 20
        ws.column_dimensions['J'].width = 20
        ws.column_dimensions['K'].width = 20
        ws.column_dimensions['L'].width = 20
        ws.column_dimensions['M'].width = 20
        ws.column_dimensions['N'].width = 25
        ws.column_dimensions['O'].width = 25
        ws.column_dimensions['P'].width = 10
        ws.column_dimensions['Q'].width = 25
        ws.column_dimensions['R'].width = 20
        ws.column_dimensions['S'].width = 30

        #设置第一行行宽度
        ws.row_dimensions[1].height = 50

        # print('设置宽度耗时：', time.time() - st)

        # st = time.time()
        # 在第一行写入标题，并设置为加粗
        titles = ['所属模块','父需求\n(默认从系统导出，否则从C列自动填写)','需求名称（必填）\n（建子需求时，需父需求评审通过）',
                  '需要描述（必填）','评审状态（必填）','类别(必填)','优先级\n（1为最高优先级别）','经营项目编号', '需求编号', '明细需求编号',
                  '是否涉及跨域协同\n(默认为：否)','影响范围','所属微服务','计划开始时间(必填)\n例：2024-09-09','计划结束时间(必填)\n例：2024-09-09',
                  '难度系数', '需求设计阶段\n(默认：需求阶段)','预估工作量(必填)\n（例：8）', '评审人（必填）']
        for col_num, title in enumerate(titles, start=1):
            cell = ws.cell(row=1, column=col_num, value=title)
            cell.font = Font(bold=True)
            cell.alignment = Alignment(wrapText=True,horizontal='center',vertical='center')
            if col_num in [3, 4, 5, 6, 14, 15, 18, 19]:
                # 必填为黄色
                cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            else:
                cell.fill = PatternFill(start_color='A0A0A0', end_color='A0A0A0', fill_type='solid')

        #第二行为示例
        examples = ['参考样例请勿删除：驾驶舱', '驾驶舱', '驾驶舱功能', '可直观的通过驾驶舱展示分析','评审通过','功能', '1', '064250HK24010080', 'GAGC8473',
                    'GAGC8473','否','全网', '绑定服务gmp-ff-pwsa', '2024-09-09', '2024-09-09', '一般', '需求', '20', 'zhangsan: 张三']
        for col_num, text in enumerate(examples, start=1):
            cell = ws.cell(row=2, column=col_num, value=text)
            cell.font = Font(color='FF0000')

        # print('前两行文本写入耗时：', time.time() - st)

        # st= time.time()
        thin_border = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))
        area = ws['$A$1:$S$50']
        for k in area:
            for h in k:
                h.border = thin_border

        # print('画框耗时：', time.time() - st)

        # 定义需要下拉框的长度
        list_length = 1000

        #对应关系表
        sheet2_name = "中间表"
        ws_middle = wb.create_sheet(title=sheet2_name)

        # st = time.time()
        story_num = 0
        #写入父需求
        for row_num, parent_story in enumerate(parent_stories, start=1):
            ws_middle.cell(row=row_num, column=1, value=parent_story)
            story_num+=1
        # print('中间表写入父需求耗时：', time.time() - st)


        #新增任务添加
        sheet1_name = "Sheet"
        # st = time.time()
        # 新增父需求写入

        # for i in range(list_length):
        #     ws_middle["$A${}".format(story_num + i+1)] = \
        #         '=IF(AND({}!$C${}<>"", {}!$B${}=""), "{}: "&{}!$C${}, "")'.format(sheet1_name,i + 3,sheet1_name,i + 3, _id+i,sheet1_name,i + 3)
        for i in range(list_length):
            ws_middle["$A${}".format(story_num + i+1)] = \
                '=IF(AND(INDIRECT("{}!$C${}")<>"", INDIRECT("{}!$B${}")=""), "{}: "&INDIRECT("{}!$C${}"), "")'.format(sheet1_name,i + 3,sheet1_name,i + 3, _id+i,sheet1_name,i + 3)
        # print('新增父需求excel函数耗时：', time.time() - st)

        #写入模块信息
        # st = time.time()
        module_num = 0
        for row_num, module in enumerate(module_names, start=1):
            module_name = ': '.join(module.split(': ')[1:])
            ws_middle.cell(row=row_num, column=6, value=module_name)
            ws_middle.cell(row=row_num, column=7, value=module)
            module_num += 1
        # print('写入模块信息耗时：', time.time() - st)

        # 写入指派人
        person_num = 0
        perosns_list = get_team(project_id)
        # 写入需求和模块对应关系
        for row_num, person in enumerate(perosns_list, start=1):
            ws_middle.cell(row=row_num, column=8, value=person)
            # ws_story2module.cell(row=row_num, column=4, value=task2story[key])
            person_num += 1

        # 有谁评审列表
        # st=time.time()
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$H$1:$H${person_num}', allow_blank=True)
        ws = set_rule(dv, 'S', ws, list_length)
        # print('数据验证耗时：', time.time() - st)

        # st = time.time()
        for i in range(story_num+list_length):

            #提取任务的id
            ws_middle["$B${}".format(i + 1)] = \
                '=IF($A${}<>"", VALUE(LEFT($A${},FIND(": ",$A${})-1)), VALUE(10000))'.format(i+1,i+1,i+1)

            #对任务id进行排序
            ws_middle["$C${}".format(i + 1)] = \
                '=RANK($B${},$B$1:$B${},1)+(COUNTIF($B$1:$B${},$B${})-1)'.format(i+1,story_num+list_length,i+1,i+1)

            #根据排名重写任务
            # for i in range(story_num+list_length):
            ws_middle["$D${}".format(i + 1)] = \
                '=INDEX($A:$A,MATCH({},$C:$C,0))'.format(i + 1)

            # 分理出名称
            # for i in range(story_num + list_length):
            ws_middle["$E${}".format(i + 1)] = \
                '=IF($D${}<>"", RIGHT($D${}, LEN($D${}) - 1 - FIND(": ", $D${})), "")'.format(i + 1, i + 1,
                                                                                              i + 1, i + 1)
        # print('excel函数父需求排名耗时：', time.time() - st)

        # 隐藏中间表
        ws_middle.sheet_state = 'hidden'

        # 启用工作表保护
        ws_middle.protection.sheet = True
        # 设置密码（可选）
        # 如果设置了密码，用户需要输入密码才能取消保护
        ws_middle.protection.password = 'zentao_export_template'

        # 设置哪些操作是被允许的，以下为默认设置，即不允许任何操作
        ws_middle.protection.enable()

        # 冻结前两行
        ws.freeze_panes = 'A3'

        # st = time.time()
        #所属模块列表
        dv = DataValidation(type="list", formula1=f'={sheet2_name}!$F$1:$F${module_num}', allow_blank=True)
        ws = set_rule(dv, 'A', ws, list_length)
        # print('所属模块数据验证耗时：', time.time() - st)

        # st = time.time()
        # 父研发需求下拉列表
        formula = '=OFFSET({}!$E$1,,,SUMPRODUCT(N(LEN({}!$E:$E)>0)),)'.format(sheet2_name, sheet2_name)
        dv = DataValidation(type="list", formula1=formula, allow_blank=True)
        ws = set_rule(dv, 'B', ws, list_length)
        # print('父研发需求数据验证耗时：', time.time() - st)

        #所属计划列表
        # dv = DataValidation(type="list", formula1='"{}"'.format(','.join(plan_names)), allow_blank=True)
        # ws = set_rule(dv, 'B', ws, list_length)

        # 评审状态列表
        # st = time.time()
        status_list = ['评审中', '评审通过', '已关闭']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(status_list)), allow_blank=True)
        ws = set_rule(dv, 'E', ws, list_length)
        # print('评审状态数据验证耗时：', time.time() - st)

        #类别列表
        # st = time.time()
        class_list = ['功能', '接口', '性能', '安全', '体验', '改进', '其他']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(class_list)), allow_blank=True)
        ws = set_rule(dv, 'F', ws, list_length)
        # print('类别数据验证耗时：', time.time() - st)

        #优先级列表
        # st = time.time()
        pri_list = ['1', '2', '3', '4']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(pri_list)), allow_blank=True)
        ws = set_rule(dv, 'G', ws, list_length)
        # print('优先级数据验证耗时：', time.time() - st)

        #难度系数列表
        # st = time.time()
        difficuty_list = ['非常重要','重要','一般','不重要']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(difficuty_list)), allow_blank=True)
        ws = set_rule(dv, 'P', ws, list_length)
        # print('难度系数数据验证耗时：', time.time() - st)

        #需求设计阶段列表
        # st = time.time()
        addStage_list = ['需求','设计']
        dv = DataValidation(type="list", formula1='"{}"'.format(','.join(addStage_list)), allow_blank=True)
        ws = set_rule(dv, 'Q', ws, list_length)
        # print('需求设计阶段数据验证耗时：', time.time() - st)

        # 预计工时只能输入数字，范围为0-8
        # st = time.time()
        dv = DataValidation(type="whole", formula1=0,formula2=1, allow_blank=True)
        ws = set_rule(dv, 'R', ws, list_length)
        # print('预计工时数据验证耗时：', time.time() - st)

        # 经营项目编号、需求编号、明细需求编号、是否涉及跨域协同、影响范围、所属微服务、
        # 计划开始时间、计划结束时间为纯文本格式
        # st = time.time()
        for i in range(list_length):
            # for j in [3,4,7,8,9,10,11,12,13,14]:
            for j in range(1, 20):
                ws.cell(row=i+2, column=j).number_format = '@'
        # print('纯文本格式数据格式耗时：', time.time() - st)

        # st = time.time()
        # 使用 io.BytesIO() 创建一个内存中的文件
        buffer = io.BytesIO()
        # 将工作簿保存到内存中的文件
        wb.save(buffer)

        # 将文件指针移到开始位置
        buffer.seek(0)

        # 设置文件名为中文，并进行URL编码
        filename = "研发需求导入模板.xlsx"
        encoded_filename = iri_to_uri(filename)

        # 创建一个HttpResponse对象，设置Content-Type和Content-Disposition
        response = HttpResponse(
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            content=buffer.getvalue()
        )
        response['Content-Disposition'] = f'attachment; filename="{encoded_filename}"'

        # 关闭buffer
        buffer.close()
        # print('buffer耗时：', time.time() - st)
        # print('总耗时：', time.time() - st1)

        return response
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#判断名称是否重复
def judge_repeat(name, keys):
    judge = False

    for key in keys:
        key_name = ''.join(key.split(': ')[1:])
        if key_name == name:
            judge = True
            break

    return judge

#判断日期格式是否有效
def is_valid_date(date_string, format_string="%Y-%m-%d %H:%M:%S"):
    try:
        return datetime.strptime(date_string, format_string)
    except ValueError:
        return False

#将任务数据转为禅道数据库格式
def task_data_trans(row,parent_id,project_id):

    #转换字段
    #任务类型
    type_map = {
        '设计': "design",
        '开发': "devel",
        '需求': "request",
        '测试': "test",
        '研究': "study",
        '讨论': "discuss",
        '界面': "ui",
        '事务': "affair",
        '其他': "misc",
        '运维': "ops",
        '告警': "warn",
        '请求': "ask",
        '咨询': "consult",
        '报障': "fault",
        '': ''
    }

    #重要程度,如果为空，默认为一般
    difficulty_map = {
        "非常重要": "veryImportant",
        "重要": "important",
        "一般": "general",
        "不重要": "notImportant",
        '': 'general'
    }

    #状态,默认为未开始
    status_map = {
        "":"wait",
        "未开始":"wait",
        "进行中": "doing",
        "已完成": "done",
        "已关闭": "closed",
        "已暂停":"pause",
        "已取消":"cancel"
    }

    #迭代转为数字
    if row[1] != '':
        execution = int(''.join(row[1].split(': ')[0]))
    else:
        execution = 0

    #模块转为数字
    if row[3] != '':
        module = int(''.join(row[3].split(': ')[0]))
    else:
        module = 0

    #需求转为数字
    if row[2] != '':
        story = int(''.join(row[2].split(': ')[0]))
    else:
        story = 0

    #pri如果为空，则默认为3
    pri = 3 if row[17] == '' else int(float(row[17]))

    #计划开始时间
    estStarted = is_valid_date(row[13], "%Y-%m-%d")
    if isinstance(estStarted, bool):
        estStarted = is_valid_date(row[13]).strftime("%Y-%m-%d")
    else:
        estStarted = estStarted.strftime("%Y-%m-%d")

    # 计划截止日期
    deadline = is_valid_date(row[14], "%Y-%m-%d")
    if isinstance(deadline, bool):
        deadline = is_valid_date(row[14]).strftime("%Y-%m-%d")
    else:
        deadline = deadline.strftime("%Y-%m-%d")

    #实际结束时间
    if row[15] == '':
        realStarted = None
    else:
        realStarted = is_valid_date(row[15], "%Y-%m-%d")
        if isinstance(realStarted, bool):
            realStarted = is_valid_date(row[15]).strftime("%Y-%m-%d %H:%M:%S")
        else:
            realStarted = realStarted.strftime("%Y-%m-%d %H:%M:%S")

    # 实际结束时间
    if row[16] == '':
        finishedDate = None
    else:
        finishedDate = is_valid_date(row[16], "%Y-%m-%d")
        if isinstance(finishedDate, bool):
            finishedDate = is_valid_date(row[16]).strftime("%Y-%m-%d %H:%M:%S")
        else:
            finishedDate = finishedDate.strftime("%Y-%m-%d %H:%M:%S")

    #创建人，默认为空
    openedBy = ''
    if row[10] != '':
        openedBy = row[10].replace(' ', '').replace('：', ':').split(':')[0]

    # 所属人，默认为空
    owner = ''
    if row[11] != '':
        owner = row[11].replace(' ', '').replace('：', ':').split(':')[0]

    res = {
        'project': int(project_id),
        # 'execution': execution,
        'module': module,
        'story': story,
        'parent': parent_id,
        'name': row[5],
        'status': status_map[row[18]],
        'type': type_map[row[7]],
        'difficulty': difficulty_map[row[8]],
        'assignedTo': row[9].replace(' ','').replace('：',':').split(':')[0],
        'openedBy': openedBy,
        'owner': owner,
        "estimate": float(row[12]),
        'estStarted': estStarted,
        'deadline': deadline,
        "realStarted": realStarted,
        "finishedDate": finishedDate,
        'desc': row[6],
        'pri': pri
    }

    return res, execution

#将需求数据转为禅道数据库格式
def story_data_trans(row,parent_id,product_id):
    #转换字段
    #状态，默认为评审中
    status_map = {
        '评审中': "reviewing",
        "评审通过": "active",
        '已关闭': "closed",
        '': 'reviewing'
    }

    #任务类型
    category_map = {
        '功能': "feature",
        '接口': "interface",
        '性能': "performance",
        '安全': "safe",
        '体验': "experience",
        '改进': "improve",
        '其他': "other",
        '': ''
    }

    #重要程度，默认为一般
    difficulty_map = {
        "非常重要": "veryImportant",
        "重要": "important",
        "一般": "general",
        "不重要": "notImportant",
        '':'general'
    }

    #需求设计阶段，默认为需求阶段
    addStage_map = {
        "需求": 'story',
        "设计": 'design',
        '':'story'
    }

    # pri如果为空，则默认为3
    pri = 3 if row[7] == '' else int(float(row[7]))

    #模块转为数字
    if row[1] != '':
        module = int(''.join(row[1].split(': ')[0]))
    else:
        module = 0

    #计划转为数字
    # if row[2] != '':
    #     plan = ''.join(row[2].split(': ')[0])
    # else:
    #     plan = ''

    #计划开始时间
    begin = is_valid_date(row[14], "%Y-%m-%d")
    if isinstance(begin, bool):
        begin = is_valid_date(row[14]).strftime("%Y-%m-%d")
    else:
        begin = begin.strftime("%Y-%m-%d")

    # 计划截止日期
    end = is_valid_date(row[15], "%Y-%m-%d")
    if isinstance(end, bool):
        end = is_valid_date(row[15]).strftime("%Y-%m-%d")
    else:
        end = end.strftime("%Y-%m-%d")

    res = {
        'product': int(product_id),
        'module': module,
        # 'plan': plan,
        'parent': parent_id,
        'title': row[3],
        'status': status_map[row[5]],
        'spec': row[4],
        'category': category_map[row[6]],
        'pri': pri,
        'businessProjectCode': row[8],
        'demandCode': row[9],
        'detailRequirementCode': row[10],
        'crossDomainCollaboration': '否' if row[11] == '' else row[11],
        'impactScope': row[12],
        'ownedMicroservice': row[13],
        'begin': begin,
        'end': end,
        'difficulty': difficulty_map[row[16]],
        'addStage': addStage_map[row[17]],
        "estimate": float(row[18])
    }

    reviewer = row[19].replace(' ','').replace('：',':').split(':')[0]

    return res, reviewer

def pymysql_import(connection, sql, values):
    # 连接数据库
    with connection.cursor() as cursor:

        # 执行 SQL 语句
        cursor.execute(sql, values)

        # 提交事务
        connection.commit()

        return cursor.lastrowid

#获取禅道token
# def get_zentao_token():
#     url = ZENTAO_URL + 'tokens'
#     data = {"account": ACCOUNT, "password": PASSWORD}
#     res = requests.post(url,json=data)
#     token = json.loads(res.text)['token']
#     return token

#导入任务数据
@timing_decorator
def import_task_template(request):

    # 检查用户是否登录
    if not has_permission(request):
        # 如果用户未登录，抛出 AuthenticationFailed 异常
        return JsonResponse({'code': 0, 'msg': '未登录'},status=401)

    if request.method == 'POST':
        # print(request.session)
        # if 'token' in request.session:
        #     del request.session['token']
        #获取文件
        file = request.FILES.get('file')
        #获取项目id
        project_id = request.POST.get('project_id', None)

        # 未上传文件
        if not (file and project_id):
            return JsonResponse({'code':0,'msg':"未上传文件或项目id"}, status=400)

        # 现有任务数据
        try:
            _, _id, task_tree = get_tasks(project_id)
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"项目现有的任务读取失败"}, status=200)

        try:
            # pymysql连接数据
            connection = pymysql.connect(**PYMYSQL_CONF)
        except:
            print(traceback.format_exc())
            return JsonResponse({'code': 0, 'msg': "无法连接到数据库"}, status=200)

        # 获取项目对应的产品
        # SQL 获取语句
        sql = "select product from zt_projectproduct where project={}".format(project_id)
        # 执行 SQL 语句
        # 连接数据库
        with connection.cursor() as cursor:
            cursor.execute(sql)
            # 使用fetchone()获取第一条记录
            product_id = cursor.fetchone()[0]

        # 一般情况下不可能
        if not product_id:
            return JsonResponse({'code': 0, 'msg': "该项目没有对应的产品"}, status=200)

        #读取数据
        try:
            #先获取列，再重新读取
            df = pd.read_excel(file)
            columns = list(df.columns)

            #重新读取
            # df = pd.read_excel(file, dtype={'计划开始时间（必填）\n（例：2024-09-09）': 'str',
            #                                 '计划截止日期（必填）\n（例：2024-09-09）': 'str',
            #                                 '实际开始日期\n（例：2024-09-09）': 'str',
            #                                 '实际结束日期\n（例：2024-09-09）': 'str',
            #                                 '优先级\n（1为最高优先级别）': 'str'})

            df = pd.read_excel(file, dtype={columns[12]: 'str',
                                            columns[13]: 'str',
                                            columns[14]: 'str',
                                            columns[15]: 'str',
                                            columns[16]: 'str'})
            df = df.dropna(axis=0, how='all')  # 删掉全为空值的行
            df = df.fillna('').astype(str)  #字符串化

            #查看第一行是否为模板数据
            examples = ['参考样例请勿删除：XXX模块迭代任务','业务流程清单', '用户管理', '业务流程清单', '业务流程清单', '实现业务流程清单功能','开发', '一般',
                    'zhangsan: 张三','zhangsan: 张三','zhangsan: 张三', '20', '2024-09-09','2024-09-09', '2024-09-09','2024-09-09', '1', '已完成']

            # 如果第一行和模板重复，则从第二行开始
            if list(df.loc[0])[0].startswith('参考样例请勿删除：'):
                df = df.iloc[1:]
            #排序，将父任务放到前面
            # df = df.sort_values(by=['父任务\n(默认从系统导出，否则从D列填写)'], ascending=[True])
            df = df.sort_values(by=[columns[3]], ascending=[True])

            #读取第二个表格
            df1 = pd.read_excel(file,sheet_name='中间表',header=None)
            df1 = df1.dropna(axis=0, how='all')  # 删掉全为空值的行
            df1 = df1.fillna('').astype(str)
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"上传的excel无法读取"}, status=200)

        #获取名称和编号的对应关系
        story2id = {'':''}
        module2id = {'':''}
        execution2id = {'':''}
        task2id = {'':''}
        for row in df1.itertuples():
            #需求
            if row[1] != '':
                story2id[row[1]] = row[7]

            #模块
            if row[9] != '':
                module2id[row[8]] = row[9]

            # 迭代
            if row[10] != '':
                execution2id[row[10]] = row[11]

            # 任务
            if row[12] != '':
                task2id[row[12]] = row[6]

        #映射到主表
        df.iloc[:, 0] = df.iloc[:, 0].map(execution2id).fillna('error_error')
        df.iloc[:, 1] = df.iloc[:, 1].map(story2id).fillna('error_error')
        df.iloc[:, 2] = df.iloc[:, 2].map(module2id).fillna('error_error')
        df.iloc[:, 3] = df.iloc[:, 3].map(task2id).fillna('error_error')

        errors = []
        error_parent = set()

        df_columns = df.columns
        #判断必填列不为空
        for i in [0, 4, 5, 6, 8, 11, 12, 13, 17]:
            if '' in list(df.iloc[:, i]):
                column = df_columns[i].split('\n')[0]
                error = '{}列有空值'.format(column)
                errors.append([0, error])

        #将数据处理为树结构,数据判断，父需求和父需求，子需求和子需求不能重复
        for row in df.itertuples():
            #任务名字
            task_name = "{}: {}".format(_id + row[0] - 1, row[5])

            #指派人必须有用户名
            reviewer_ls = row[9].replace(' ','').replace('：',':').split(':')
            if len(reviewer_ls) == 2:
                #第一个必须为全英文和数字，才是用户名
                if not (bool(re.fullmatch(r'[a-zA-Z0-9]+', reviewer_ls[0])) and reviewer_ls[1] != ''):
                    error = '第{}行指派人用户名不正确，请使用下拉框选择'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
            else:
                error = '第{}行指派人不符合要求，请使用下拉框选择'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 创建人必须有用户名
            if row[10] != '':
                reviewer_ls = row[10].replace(' ', '').replace('：', ':').split(':')
                if len(reviewer_ls) == 2:
                    # 第一个必须为全英文和数字，才是用户名
                    if not (bool(re.fullmatch(r'[a-zA-Z0-9]+', reviewer_ls[0])) and reviewer_ls[1] != ''):
                        error = '第{}行创建人用户名不正确，请使用下拉框选择'.format(row[0] + 2)
                        errors.append([row[0], error])
                        continue
                else:
                    error = '第{}行创建人不符合要求，请使用下拉框选择'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue

            # 所属人必须有用户名
            if row[11] != '':
                reviewer_ls = row[11].replace(' ', '').replace('：', ':').split(':')
                if len(reviewer_ls) == 2:
                    # 第一个必须为全英文和数字，才是用户名
                    if not (bool(re.fullmatch(r'[a-zA-Z0-9]+', reviewer_ls[0])) and reviewer_ls[1] != ''):
                        error = '第{}行所属人用户名不正确，请使用下拉框选择'.format(row[0] + 2)
                        errors.append([row[0], error])
                        continue
                else:
                    error = '第{}行所属人不符合要求，请使用下拉框选择'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue

            #error表示找不到对应的序号，即不存在
            if row[1] == 'error_error':
                error = '第{}行迭代任务名称不存在'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if row[2] == 'error_error':
                error = '第{}行研发需求不存在'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if row[3] == 'error_error':
                error = '第{}行功能模块不存在'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if row[4] == 'error_error':
                error = '第{}行父任务不存在'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            #任务类型需在列表内
            type_list = ['设计', '开发', '需求', '测试', '研究', '讨论', '界面', '事务', '运维', '告警', '请求', '咨询', '报障', '其他', '实施']
            if row[7] not in type_list:
                error = '第{}行任务类型值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 难度系数需在列表内
            difficuty_list = ['非常重要','重要','一般','不重要','']
            if row[8] not in difficuty_list:
                error = '第{}行难度系数值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 优先级需在列表内
            pri_list = ['1','2','3','4','']
            if row[17] not in pri_list:
                error = '第{}行优先级值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 状态需在列表内
            status_list = ['未开始', '进行中', '已完成', '已关闭', '已暂停', '已取消']
            if row[18] not in status_list:
                error = '第{}行状态值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 预估工作量需在80以内
            try:
                if float(row[12]) > 80:
                    error = '第{}行预估工作量需小于等于80'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
            except:
                error = '第{}行预估工作量需为数值'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if isinstance(is_valid_date(row[13], "%Y-%m-%d"),bool) and isinstance(is_valid_date(row[13]),bool):
                error = '第{}行计划开始时间格式需为2024-09-09'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if isinstance(is_valid_date(row[14], "%Y-%m-%d"),bool) and isinstance(is_valid_date(row[14]),bool):
                error = '第{}行计划截止日期格式需为2024-09-09'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if isinstance(is_valid_date(row[15], "%Y-%m-%d"),bool) and isinstance(is_valid_date(row[15]),bool) and row[15]!='':
                error = '第{}行实际开始日期格式需为2024-09-09 05:02:22'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if isinstance(is_valid_date(row[16], "%Y-%m-%d"),bool) and isinstance(is_valid_date(row[16]),bool) and row[16]!='':
                print([row[16]])
                error = '第{}行实际结束日期格式需为2024-09-09 05:02:22'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            #父子任务名称不能一样
            parent_name = ''.join(row[4].split(': ')[1:])
            if parent_name == row[5]:
                error = '第{}行父任务和子任务名称重复'.format(row[0]+2)
                errors.append([row[0],error])
                continue

            #判断父任务是否重复
            #如果父任务为空，则新添加的任务为父任务，否则为子任务
            if row[4] == '':
                if judge_repeat(row[5], task_tree.keys()):
                    error_parent.add(task_name)
                    error = '第{}行任务与已有父任务名称重复'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
                else:
                    task_tree[task_name] = {'add':"","data":row,"child":{}}
            else:

                #如果父任务异常，则这条直接标为异常
                if row[4] in error_parent:
                    error = '第{}行父任务为重复的父任务'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
                else:
                    #查看子任务是否重复
                    child_tasks = task_tree[row[4]]['child']
                    if judge_repeat(row[5], child_tasks.keys()):
                        error = '第{}行任务与该父任务对应的已有子任务名称重复'.format(row[0] + 2)
                        errors.append([row[0], error])
                        continue
                    else:
                        task_tree[row[4]]['child'][task_name] = {'add': "", "data": row, "child": {}}

        errors = sorted(errors,key=lambda x:int(x[0]))
        errors = [error[1] for error in errors]
        #如果error不为空，则返回错误信息
        if len(errors) != 0:
            res = {
                'code': 0,
                'msg': "数据不合规",
                'errors': '\n'.join(errors)
            }
            return JsonResponse(res, status=200)

        #获取token
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        login_account = request.session['account']

        try:
            #通过检测的数据，调用禅道接口进行添加
            for key,value in task_tree.items():
                if 'add' in value.keys():
                    data, execution = task_data_trans(value['data'],0, project_id)

                    value['parent'] = 0

                    #完成人，默认为空
                    finishedBy = ''
                    if data['status'] in ['done','closed']:
                        finishedBy = data['assignedTo']

                    # 创建人，默认为空
                    openedBy = login_account
                    if data['openedBy'] != '':
                        openedBy = data['openedBy']

                    #如果状态为关闭
                    if data['status'] == 'closed':
                        closedBy = login_account
                        closedDate = current_time
                    else:
                        closedBy = ''
                        closedDate = None

                    #如果状态为取消
                    if data['status'] == 'cancel':
                        canceledBy = login_account
                        canceledDate = current_time
                    else:
                        canceledBy = ''
                        canceledDate = None

                    # url = ZENTAO_URL + 'executions/{}/tasks'.format(execution)
                    # response = requests.post(url, json=data, headers=headers)
                    # parent_id = json.loads(response.text)['id']

                    sql = "INSERT INTO `zt_task` " \
                          "(`project`, `parent`, `execution`, `module`, `design`, `story`, `storyVersion`,`designVersion`," \
                          "`fromBug`,`feedback`,`fromIssue`,`name`,`type`,`difficulty`,`owner`,`pri`,`estimate`,`consumed`,`left`," \
                          "`deadline`,`status`,`desc`,`version`,`openedBy`,`openedDate`,`assignedTo`,`assignedDate`,`estStarted`," \
                          "`realStarted`,`finishedDate`,`planDuration`,`realDuration`,`order`,`repo`,`mr`,`deleted`,`vision`,`finishedBy`,`closedBy`,`closedDate`,`canceledBy`,`canceledDate`)" \
                          " VALUES " \
                          "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
                          " %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    values = (project_id, 0, execution, data['module'], 0, 0, 1, 1, 0, 0, 0, data['name'], data['type'],
                              data['difficulty'],data['owner'], data['pri'], data['estimate'], 0, data['estimate'], data['deadline'],
                              data['status'], data['desc'], 1, openedBy, current_time, data['assignedTo'], current_time,
                              data['estStarted'],data['realStarted'],data['finishedDate'], 0, 0, 0, 0, 0, '0', 'rnd',finishedBy,closedBy,closedDate,canceledBy,canceledDate)
                    parent_id = pymysql_import(connection, sql, values)

                    # 在zt_action中标识是谁插入的
                    # SQL 插入语句
                    sql = "INSERT INTO `zt_action` " \
                          "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                          "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                          " VALUES " \
                          "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    values = (
                        'task', parent_id, f',{product_id},', project_id, execution, openedBy, 'opened', current_time,
                        '', '', '0', 'rnd', 0)
                    pymysql_import(connection, sql, values)

                    # 在zt_action中标识是指派给谁的
                    # SQL 插入语句
                    sql = "INSERT INTO `zt_action` " \
                          "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                          "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                          " VALUES " \
                          "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    values = (
                        'task', parent_id, f',{product_id},', project_id, execution, login_account, 'assigned',
                        current_time,
                        '', data['assignedTo'], '0', 'rnd', 0)
                    pymysql_import(connection, sql, values)

                    # 标识为插入
                    sql = "INSERT INTO `zt_action` " \
                          "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                          "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                          " VALUES " \
                          "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    values = (
                        'task-import', parent_id, f',{product_id},', project_id, execution, login_account, 'import',
                        current_time,
                        '', '', '0', 'rnd', 0)
                    pymysql_import(connection, sql, values)

                    #如果是完成和关闭，则标识为关闭
                    if data['status'] in ['closed','done']:
                        # SQL 插入语句
                        sql = "INSERT INTO `zt_action` " \
                              "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                              "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                              " VALUES " \
                              "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                        # 要插入的数据
                        values = (
                            'task', parent_id, f',{product_id},', project_id, execution, data['assignedTo'], 'finished',
                            current_time,
                            '', '', '0', 'rnd', 0)
                        pymysql_import(connection, sql, values)

                else:
                    parent_id = value['id']

                if len(value['child']) != 0:

                    #如果父节点的parent为0，则改为-1
                    # 修改父节点parent id
                    if value['parent'] == 0:
                        # ZtTask.objects.filter(id=parent_id).update(parent=-1)
                        sql = """
                            UPDATE `zt_task`
                            SET `parent` = %s
                            WHERE `id` = %s
                            """
                        values = (-1, parent_id)
                        pymysql_import(connection, sql, values)

                    for child_key, child_value in value['child'].items():
                        if 'add' in child_value.keys():
                            #插入新任务，只能插父节点
                            data, execution = task_data_trans(child_value['data'], parent_id, project_id)

                            # url = ZENTAO_URL + 'executions/{}/tasks'.format(execution)
                            # response = requests.post(url, json=data, headers=headers)
                            # 完成人，默认为空
                            finishedBy = ''
                            if data['status'] in ['done','closed']:
                                finishedBy = data['assignedTo']

                            # 创建人，默认为空
                            openedBy = login_account
                            if data['openedBy'] != '':
                                openedBy = data['openedBy']

                            # 如果状态为关闭
                            if data['status'] == 'closed':
                                closedBy = login_account
                                closedDate = current_time
                            else:
                                closedBy = ''
                                closedDate = None

                            # 如果状态为取消
                            if data['status'] == 'cancel':
                                canceledBy = login_account
                                canceledDate = current_time
                            else:
                                canceledBy = ''
                                canceledDate = None

                            sql = "INSERT INTO `zt_task` " \
                                  "(`project`, `parent`, `execution`, `module`, `design`, `story`, `storyVersion`,`designVersion`," \
                                  "`fromBug`,`feedback`,`fromIssue`,`name`,`type`,`difficulty`, `owner`,`pri`,`estimate`,`consumed`,`left`," \
                                  "`deadline`,`status`,`desc`,`version`,`openedBy`,`openedDate`,`assignedTo`,`assignedDate`,`estStarted`," \
                                  "`realStarted`,`finishedDate`,`planDuration`,`realDuration`,`order`,`repo`,`mr`,`deleted`,`vision`,`finishedBy`,`closedBy`,`closedDate`,`canceledBy`,`canceledDate`)" \
                                  " VALUES " \
                                  "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
                                  " %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = (project_id, parent_id, execution, data['module'], 0, 0, 1, 1, 0, 0, 0, data['name'], data['type'],
                                    data['difficulty'],data['owner'], data['pri'], data['estimate'], 0, data['estimate'], data['deadline'],
                                    data['status'], data['desc'], 1, openedBy, current_time, data['assignedTo'], current_time,
                                    data['estStarted'],data['realStarted'],data['finishedDate'], 0, 0, 0, 0, 0, '0', 'rnd',finishedBy,closedBy,closedDate,canceledBy,canceledDate)
                            child_id = pymysql_import(connection, sql, values)

                            # 在zt_action中标识是谁插入的
                            # SQL 插入语句
                            sql = "INSERT INTO `zt_action` " \
                                  "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                                  "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                                  " VALUES " \
                                  "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = (
                                'task', child_id, f',{product_id},', project_id, execution, openedBy, 'opened',
                                current_time,
                                '', '', '0', 'rnd', 0)
                            pymysql_import(connection, sql, values)

                            # 在zt_action中标识是指派给谁的
                            # SQL 插入语句
                            sql = "INSERT INTO `zt_action` " \
                                  "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                                  "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                                  " VALUES " \
                                  "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = (
                                'task', parent_id, f',{product_id},', project_id, execution, login_account, 'assigned',
                                current_time,
                                '', data['assignedTo'], '0', 'rnd', 0)
                            pymysql_import(connection, sql, values)

                            # 标识为插入
                            sql = "INSERT INTO `zt_action` " \
                                  "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                                  "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                                  " VALUES " \
                                  "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = (
                                'task-import', child_id, f',{product_id},', project_id, execution, login_account,
                                'import',
                                current_time,
                                '', '', '0', 'rnd', 0)
                            pymysql_import(connection, sql, values)

                            # 如果是完成和关闭，则标识为关闭
                            if data['status'] in ['closed', 'done']:
                                # SQL 插入语句
                                sql = "INSERT INTO `zt_action` " \
                                      "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                                      "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                                      " VALUES " \
                                      "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                                # 要插入的数据
                                values = (
                                    'task', child_id, f',{product_id},', project_id, execution, data['assignedTo'],
                                    'finished',
                                    current_time,
                                    '', '', '0', 'rnd', 0)
                                pymysql_import(connection, sql, values)

                            # 插入后父节点默认为0，修改子节点parent id
                            # ZtTask.objects.filter(id=json.loads(response.text)['id']).update(parent=parent_id)
                            # sql = """
                            #     UPDATE `zt_story`
                            #     SET `parent` = %s
                            #     WHERE `id` = %s
                            #     """
                            # values = (parent_id, json.loads(response.text)['id'])
                            # pymysql_import(connection, sql, values)
                            # url = ZENTAO_URL + 'tasks/{}'.format(json.loads(response.text)['id'])
                            # response = requests.post(url, json={'parent': parent_id}, headers=headers)

        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"数据导入报错"}, status=200)

        #关闭连接
        try:
            connection.close()
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"数据库连接无法关闭"}, status=200)

        res = {
            'code': 1,
            'msg': "导入成功"
        }

        return JsonResponse(res, status=200)

    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#导入任务工时数据
@timing_decorator
def import_task_estimate_template(request):

    # 检查用户是否登录
    if not has_permission(request):
        # 如果用户未登录，抛出 AuthenticationFailed 异常
        return JsonResponse({'code': 0, 'msg': '未登录'},status=401)

    if request.method == 'POST':
        # print(request.session)
        # if 'token' in request.session:
        #     del request.session['token']
        #获取文件
        file = request.FILES.get('file')

        # 未上传文件
        if not file:
            return JsonResponse({'code':0,'msg':"未上传文件"}, status=400)

        try:
            # pymysql连接数据
            connection = pymysql.connect(**PYMYSQL_CONF)
        except:
            print(traceback.format_exc())
            return JsonResponse({'code': 0, 'msg': "无法连接到数据库"}, status=200)

        #读取数据
        try:

            #先获取列，再重新读取
            df = pd.read_excel(file)
            columns = list(df.columns)

            # df = pd.read_excel(file, dtype={'预计工时\n（模板自动计算，请勿修改）': 'str',
            #                                 '已消费工时\n（模板自动计算，请勿修改）': 'str',
            #                                 '剩余工时\n（模板自动计算，请勿修改）': 'str',
            #                                 '工时日期（必填）': 'str',
            #                                 '消耗工时（必填）': 'str'})

            df = pd.read_excel(file, dtype={columns[2]: 'str',
                                            columns[3]: 'str',
                                            columns[4]: 'str',
                                            columns[5]: 'str',
                                            columns[7]: 'str'})

            df = df.dropna(axis=0, how='all')  # 删掉全为空值的行
            df = df.fillna('').astype(str)  #字符串化

            #查看第一行是否为模板数据
            examples = ['参考样例请勿删除：zhangsan: 张三','样例：任务1', '20', '5', '15', '2020-09-06','工作内容干了什么','8']
            #如果第一行和模板重复，则从第二行开始
            if list(df.loc[0])[0].startswith('参考样例请勿删除：'):
                df = df.iloc[1:]

            #读取第二个表格
            df1 = pd.read_excel(file,sheet_name='中间表',header=None)
            df1 = df1.dropna(axis=0, how='all')  # 删掉全为空值的行
            df1 = df1.fillna('').astype(str)
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"上传的excel无法读取"}, status=200)

        #获取名称和编号的对应关系
        task2id = {'':''}
        for row in df1.itertuples():
            # 任务
            if row[1] != '':
                task2id[row[1]] = row[2]

        #映射到主表
        df.iloc[:, 1] = df.iloc[:, 1].map(task2id).fillna('error_error')

        errors = []
        task_ids = set()

        df_columns = df.columns
        #判断必填列不为空
        for i in [0, 1,2,3,4,5,6,7]:
            if '' in list(df.iloc[:, i]):
                column = df_columns[i].split('\n')[0]
                error = '{}列有空值'.format(column)
                errors.append([0, error])

        #将数据处理为树结构,数据判断，父需求和父需求，子需求和子需求不能重复
        for row in df.itertuples():

            #指派人必须有用户名
            account_ls = row[1].replace(' ','').replace('：',':').split(':')
            # print(account_ls)
            if len(account_ls) == 2:
                #第一个必须为全英文和数字，才是用户名
                if not (bool(re.fullmatch(r'[a-zA-Z0-9]+', account_ls[0])) and account_ls[1] != ''):
                    error = '第{}行指派人用户名不正确，请使用下拉框选择'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
            else:
                error = '第{}行指派人不符合要求，请使用下拉框选择'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            #error表示找不到对应的序号，即不存在
            if row[2] == 'error_error':
                error = '第{}行任务名称不存在'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if isinstance(is_valid_date(row[6], "%Y-%m-%d"),bool) and isinstance(is_valid_date(row[6]),bool):
                error = '第{}行工时日期时间格式需为2024-09-09'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 工时必须为数字
            try:
                float(row[8])
            except:
                error = '第{}行工时必须为数字'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 工时必须为正数
            if float(row[8]) <=0:
                error = '第{}行工时必须大于0'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            #工时不能超过预计工时25%
            if (float(row[8])+float(row[4])) / float(row[3]) > 1.25:
                error = '第{}行工时超过预计工时25%'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            task_ids.add(row[2].split(': ')[0])

        if df.shape[0] == 0:
            error = '模板数据为空'
            errors.append([0, error])

        errors = sorted(errors,key=lambda x:int(x[0]))
        errors = [error[1] for error in errors]
        #如果error不为空，则返回错误信息
        if len(errors) != 0:
            res = {
                'code': 0,
                'msg': "数据不合规",
                'errors': '\n'.join(errors)
            }
            return JsonResponse(res, status=200)

        #获取token
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        login_account = request.session['account']

        #筛选task对应的ptoject、product、execution
        task_detail = {}
        sql = f'''
        SELECT 
            t.id AS task_id,
            t.project AS project_id,
            t.execution AS execution,
            pp.product AS product_id
        FROM 
            zt_task t
        JOIN 
            zt_projectproduct pp ON t.project = pp.project 
        
        WHERE 
            t.deleted = '0' AND id in ({",".join(task_ids)}); 
        '''

        # pymysql连接数据
        # connection = pymysql.connect(**PYMYSQL_CONF)
        # 执行 SQL 语句
        # 连接数据库
        with connection.cursor() as cursor:
            cursor.execute(sql)
            data = cursor.fetchall()

        for row in data:
            if row[0] not in task_detail.keys():
                task_detail[row[0]] = {"project":row[1],"product":row[3],'execution':row[2]}

        try:
            #通过检测的数据，调用禅道接口进行添加
            for row in df.itertuples():

                account = row[1].split(': ')[0]
                task_id = int(row[2].split(': ')[0])
                estimate = row[3]
                now_estimate = float(row[8].strip())
                # print([row[8],row[8].strip(),float(row[8]),float(row[8].strip())])
                consumed = float(row[4]) + now_estimate
                left = float(estimate) - consumed
                # 计划开始时间
                estimate_date = is_valid_date(row[6], "%Y-%m-%d")
                if isinstance(estimate_date, bool):
                    estimate_date = is_valid_date(row[6]).strftime("%Y-%m-%d")
                else:
                    estimate_date = estimate_date.strftime("%Y-%m-%d")
                work = row[7]
                project = task_detail[task_id]['project']
                product = f",{task_detail[task_id]['product']},"
                execution = task_detail[task_id]['execution']

                # #插入工时信息
                sql = "INSERT INTO `zt_effort` " \
                      "(`objectType`, `objectID`, `product`, `project`, `execution`, `account`, `work`,`vision`," \
                      "`date`,`left`,`consumed`,`order`,`deleted`)" \
                      " VALUES " \
                      "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                # 要插入的数据
                values = ('task', task_id, product, project, execution, account, work, 'rnd', estimate_date, left, now_estimate, 0, '0')

                insert_id = pymysql_import(connection, sql, values)

                # 标识为插入
                sql = "INSERT INTO `zt_action` " \
                      "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                      "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                      " VALUES " \
                      "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                # 要插入的数据
                values = (
                    'effort-import', insert_id, f'{product}', project, execution, login_account,
                    'import',
                    current_time,
                    '', '', '0', 'rnd', 0)
                pymysql_import(connection, sql, values)

                # #更新zt_taskteam表的消费工时和剩余工时
                # sql = """
                #     UPDATE `zt_taskteam`
                #     SET `consumed` = %s, `left` = %s
                #     WHERE `task` = %s AND `account` = %s
                #     """
                # values = (consumed, left, task_id, account)
                # pymysql_import(connection, sql, values)

            # 更新zt_task表的消费工时和剩余工时，先查找zt_effort中的消耗工时和剩余工时
            task_detail = {}
            sql = f'''
                SELECT 
                    objectID,
                    account,
                    consumed
                FROM 
                    zt_effort
                WHERE 
                    objectType = 'task' AND objectID in ({",".join(task_ids)}) AND deleted='0'; 
                '''

            # pymysql连接数据
            connection = pymysql.connect(**PYMYSQL_CONF)
            # 执行 SQL 语句
            # 连接数据库
            with connection.cursor() as cursor:
                cursor.execute(sql)
                data = cursor.fetchall()

            task_estimate = {}
            task_team_estimate = {}
            for row in data:
                # print(row)
                if row[0] not in task_estimate.keys():
                    task_estimate[row[0]] = 0
                if row[0] not in task_team_estimate.keys():
                    task_team_estimate[row[0]] = 0

                #该任务总消耗工时
                task_estimate[row[0]] += row[2]

                #该任务该用户消耗工时数
                if row[1] == account:
                    task_team_estimate[row[0]] += row[2]

            #更新zt_task
            for key, value in task_estimate.items():
                sql = f'''
                UPDATE zt_task
                SET consumed = %s
                WHERE id = %s;
                '''
                values = (value, key)
                pymysql_import(connection, sql, values)

            #更新剩余的
            sql = f'''
                    UPDATE zt_task
                    SET `left` = CASE
                       WHEN estimate - consumed < 0 THEN 0
                       ELSE estimate - consumed
                        END
                    WHERE id in ({",".join(task_ids)});
                    '''
            with connection.cursor() as cursor:
                cursor.execute(sql)
                connection.commit()

            # 更新zt_taskteam
            for key, value in task_team_estimate.items():
                sql = f'''
                        UPDATE zt_taskteam
                        SET consumed = %s
                        WHERE task = %s AND account = %s;
                        '''
                values = (value,key,account)
                pymysql_import(connection, sql, values)
                # print(sql)
                # print(values)
            # 更新剩余的
            sql = f'''
                    UPDATE zt_taskteam
                    SET `left` = estimate - consumed
                    WHERE task in ({",".join(task_ids)}) AND account = '{account}';
                    '''
            with connection.cursor() as cursor:
                cursor.execute(sql)
                connection.commit()

        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"数据导入报错"}, status=200)

        #关闭连接
        try:
            connection.close()
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"数据库连接无法关闭"}, status=200)

        res = {
            'code': 1,
            'msg': "导入成功"
        }

        return JsonResponse(res, status=200)

    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#导入需求数据
@timing_decorator
def import_story_template(request):

    # 检查用户是否登录
    if not has_permission(request):
        # 如果用户未登录，抛出 AuthenticationFailed 异常
        return JsonResponse({'code': 0, 'msg': '未登录'},status=401)

    if request.method == 'POST':
        # print(request.session)
        # if 'token' in request.session:
        #     del request.session['token']
        #获取文件
        file = request.FILES.get('file')
        #获取项目id
        project_id = request.POST.get('project_id', None)

        # 未上传文件
        if not (file and project_id):
            return JsonResponse({'code':0,'msg':"未上传文件或项目id"}, status=400)

        # 获取项目是否为产品型项目
        project = ZtProject.objects.filter(id=project_id,deleted='0').values('hasProduct').distinct().first()

        if not project:
            return JsonResponse({'code': 0, 'msg': '该项目不存在'}, status=200)

        # st = time.time()
        #获取项目对应的产品
        # 现有需求数据
        try:
            _id, story_tree = get_story_tree(project_id)
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"项目现有的需求读取失败"}, status=200)

        try:
            #pymysql连接数据
            connection = pymysql.connect(**PYMYSQL_CONF)
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"无法连接到数据库"}, status=200)

        # 获取项目对应的产品
        # SQL 获取语句
        sql = "select product from zt_projectproduct where project={}".format(project_id)
        # 执行 SQL 语句
        # 连接数据库
        with connection.cursor() as cursor:
            cursor.execute(sql)
            # 使用fetchone()获取第一条记录
            product_id = cursor.fetchone()[0]

        #一般情况下不可能
        if not product_id:
            return JsonResponse({'code': 0, 'msg': "该项目没有对应的产品"}, status=200)

        try:

            #先获取列，再重新读取
            df = pd.read_excel(file)
            columns = list(df.columns)

            #读取数据
            # df = pd.read_excel(file, dtype={'优先级\n（1为最高优先级别）': 'str',
            #                                 '计划开始时间(必填)\n例：2024-09-09': 'str',
            #                                 '计划结束时间(必填)\n例：2024-09-09': 'str'
            #                                 })
            df = pd.read_excel(file, dtype={columns[6]: 'str',
                                            columns[13]: 'str',
                                            columns[14]: 'str'
                                            })

            df = df.dropna(axis=0, how='all')  # 删掉全为空值的行
            df = df.fillna('').astype(str)  # 字符串化
            # 查看第一行是否为模板数据
            examples = ['参考样例请勿删除：驾驶舱', '驾驶舱', '驾驶舱功能', '可直观的通过驾驶舱展示分析','评审通过','功能', '1', '064250HK24010080', 'GAGC8473',
                    'GAGC8473','否','全网', '绑定服务gmp-ff-pwsa', '2024-09-09', '2024-09-09', '一般', '需求', '20', 'zhangsan: 张三']
            # 如果第一行和模板重复，则从第二行开始
            if list(df.loc[0])[0].startswith('参考样例请勿删除：'):
                df = df.iloc[1:]
            #排序，将父需求放到前面
            # df = df.sort_values(by=['父需求\n(默认从系统导出，否则从C列自动填写)'], ascending=[True])
            df = df.sort_values(by=[columns[1]], ascending=[True])

            #读取第二个表格
            df1 = pd.read_excel(file,sheet_name='中间表',header=None)
            df1 = df1.dropna(axis=0, how='all')  # 删掉全为空值的行
            df1 = df1.fillna('').astype(str)

        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"上传的excel无法读取"}, status=200)

        #获取名称和编号的对应关系
        story2id = {'':''}
        module2id = {'':''}

        for row in df1.itertuples():
            #需求
            if row[5] != '':
                story2id[row[5]] = row[4]

            #模块
            if row[6] != '':
                module2id[row[6]] = row[7]

        #映射到主表
        df.iloc[:, 1] = df.iloc[:, 1].map(story2id).fillna('error_error')
        df.iloc[:, 0] = df.iloc[:, 0].map(module2id).fillna('error_error')

        errors = []
        error_parent = set()

        df_columns = df.columns
        #判断必填列不为空
        for i in [2, 3, 4, 5, 13, 14, 17, 18]:
            if '' in list(df.iloc[:, i]):
                column = df_columns[i].split('\n')[0]
                error = '{}列有空值'.format(column)
                errors.append([0, error])

        #将数据处理为树结构,数据判断，父需求和父需求，子需求和子需求不能重复
        for row in df.itertuples():
            #需求名字
            story_name = "{}: {}".format(_id + row[0] - 1, row[3])
            # print(story_name)

            #评审人必须有用户名
            reviewer_ls = row[-1].replace(' ','').replace('：',':').split(':')
            if len(reviewer_ls) == 2:
                #第一个必须为全英文和数字，才是用户名
                if not (bool(re.fullmatch(r'[a-zA-Z0-9]+', reviewer_ls[0])) and reviewer_ls[1] != ''):
                    error = '第{}行评审人用户名不正确，请使用下拉框选择'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
            else:
                error = '第{}行评审人不符合要求，请使用下拉框选择'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            #error表示找不到对应的序号，即不存在
            if row[1] == 'error_error':
                error = '第{}行所属模块不存在'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if row[2] == 'error_error':
                error = '第{}行父需求不存在'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 类别需在列表内
            class_list = ['功能', '接口', '性能', '安全', '体验', '改进', '其他']
            if row[6] not in class_list:
                error = '第{}行类别值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 难度系数需在列表内
            difficuty_list = ['非常重要', '重要', '一般', '不重要','']
            if row[16] not in difficuty_list:
                error = '第{}行难度系数值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 难度系数需在列表内
            addStage_list = ['需求','设计','']
            if row[17] not in addStage_list:
                error = '第{}行需求设计阶段值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 优先级需在列表内
            pri_list = ['1', '2', '3', '4','']
            if row[7] not in pri_list:
                error = '第{}行优先级值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            # 评审状态需在列表内
            status_list = ['评审中', '评审通过', '已关闭']
            if row[5] not in status_list:
                error = '第{}行评审状态值需在下拉框内'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if isinstance(is_valid_date(row[14], "%Y-%m-%d"),bool) and isinstance(is_valid_date(row[14]),bool):
                error = '第{}行计划开始时间格式需为2024-09-09'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            if isinstance(is_valid_date(row[15], "%Y-%m-%d"),bool) and isinstance(is_valid_date(row[15]),bool):
                error = '第{}行计划结束时间格式需为2024-09-09'.format(row[0] + 2)
                errors.append([row[0], error])
                continue

            #父子需求名称不能一样
            parent_name = ''.join(row[2].split(': ')[1:])

            if parent_name == row[3]:
                error = '第{}行父任务和子任务名称重复'.format(row[0]+2)
                errors.append([row[0],error])
                continue

            #判断父需求是否重复
            #如果父需求为空，则新添加的任务为父需求，否则为子需求
            if row[2] == '':
                if judge_repeat(row[3], story_tree.keys()):
                    error_parent.add(story_name)
                    error = '第{}行需求与已有父需求名称重复'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
                else:
                    story_tree[story_name] = {'add':"","data":row,'status':row[5],"child":{}}
            else:

                #如果父任务异常，则这条直接标为异常
                if row[2] in error_parent:
                    error = '第{}行父需求为重复的父需求'.format(row[0] + 2)
                    errors.append([row[0], error])
                    continue
                else:

                    # 如果为新增的父需求并且评审状态不为评审完成则报错
                    if 'add' in story_tree[row[2]] and story_tree[row[2]]['status'] != '评审通过':
                        error = '第{}行的父需求评审状态不为评审通过'.format(row[0] + 2)
                        errors.append([row[0], error])
                        continue

                    #如果为新增的父需求并且所属计划不为空则报错
                    # if 'add' in story_tree[row[2]] and story_tree[row[2]]['plan'] != '':
                    #     error = '第{}行的父需求的所属计划不为空'.format(row[0] + 2)
                    #     errors.append([row[0], error])
                    #     continue

                    #查看子任务是否重复
                    child_tasks = story_tree[row[2]]['child']
                    if judge_repeat(row[3], child_tasks.keys()):
                        error = '第{}行需求与该父需求对应的已有子需求名称重复'.format(row[0] + 2)
                        errors.append([row[0], error])
                        continue
                    else:
                        story_tree[row[2]]['child'][story_name] = {'add': "", "data": row, "child": {}}

        errors = sorted(errors,key=lambda x:int(x[0]))
        errors = [error[1] for error in errors]

        #如果error不为空，则返回错误信息
        if len(errors) != 0:
            res = {
                'code': 0,
                'msg': "数据不合规",
                'errors':'\n'.join(errors)
            }
            return JsonResponse(res, status=200)

        #获取token
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        login_account = request.session['account']

        try:
            #通过检测的数据，调用禅道接口进行添加
            for key,value in story_tree.items():
                if 'add' in value.keys():
                    data, reviewer = story_data_trans(value['data'],0, product_id)

                    value['parent'] = 0
                    value['status'] = data['status']

                    # 如果为产品性项目，状态默认为wait，否则为projected
                    if project['hasProduct'] == 1:
                        stage = 'wait'
                    else:
                        stage = 'projected'

                    #如果状态为关闭
                    if data['status'] == 'closed':
                        closedBy = login_account
                        closedDate = current_time
                    else:
                        closedBy = ''
                        closedDate = None

                    #插入需求
                    # url = ZENTAO_URL + 'stories'
                    # response = requests.post(url, json=data, headers=headers)
                    # story_info = json.loads(response.text)
                    # parent_id = story_info['id']
                    # SQL 插入语句
                    sql = "INSERT INTO `zt_story` " \
                          "(`vision`, `parent`, `product`, `branch`, `module`, `plan`, `businessProjectCode`,`demandCode`," \
                          "`detailRequirementCode`,`crossDomainCollaboration`,`impactScope`,`ownedMicroservice`,`difficulty`," \
                          "`addStage`,`begin`,`end`,`fromBug`,`feedback`,`title`,`type`,`category`,`pri`,`estimate`,`status`," \
                          "`stage`,`lib`,`fromStory`,`fromVersion`,`openedBy`,`openedDate`,`toBug`,`duplicateStory`,`version`," \
                          "`storyChanged`,`demand`,`URChanged`,`deleted`,`closedBy`,`closedDate`)" \
                          " VALUES " \
                          "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
                          " %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    values = ('rnd', 0, product_id, 0, data['module'], '', data['businessProjectCode'], data['demandCode'],
                              data['detailRequirementCode'], data['crossDomainCollaboration'], data['impactScope'],
                              data['ownedMicroservice'], data['difficulty'], data['addStage'], data['begin'], data['end'],
                              0, 0, data['title'], 'story', data['category'], data['pri'], data['estimate'], data['status'],
                              stage, 0, 0, 1, login_account, current_time, 0, 0, 1, '0', 0, '0', '0',closedBy, closedDate)
                    parent_id = pymysql_import(connection, sql, values)

                    # 标识为插入
                    sql = "INSERT INTO `zt_action` " \
                          "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                          "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                          " VALUES " \
                          "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    values = (
                    'story-import', parent_id, f',{product_id},', int(project_id), 0, login_account, 'import', current_time,
                    '', '', '0', 'rnd', 0)
                    pymysql_import(connection, sql, values)

                    #在zt_action中标识是谁插入的
                    # SQL 插入语句
                    sql = "INSERT INTO `zt_action` " \
                          "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                          "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                          " VALUES " \
                          "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    values = ('story', parent_id, f',{product_id},', int(project_id), 0, login_account, 'opened', current_time,
                              '', '', '0', 'rnd', 0)
                    pymysql_import(connection, sql, values)

                    # 如果状态为激活（评审通过），则插入评审action
                    if data['status'] == 'active':
                        # 在zt_action中标识是谁插入的
                        # SQL 插入语句
                        sql = "INSERT INTO `zt_action` " \
                              "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                              "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                              " VALUES " \
                              "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                        # 要插入的数据
                        values = (
                        'story', parent_id, f',{product_id},', project_id, 0, reviewer, 'reviewed', current_time,
                        '', 'Pass', '0', 'rnd', 0)
                        pymysql_import(connection, sql, values)

                    #django修改字段plan和status
                    # ZtStory.objects.filter(id=parent_id).update(status=data['status'], stage='projected')
                    # sql = """
                    #     UPDATE `zt_story`
                    #     SET `plan` = %s, `status` = %s, `stage` = %s
                    #     WHERE `id` = %s
                    #     """
                    # values = ('', data['status'], 'projected', parent_id)
                    # pymysql_import(connection, sql, values)

                    # 还需在zt_storyreview插入对应的关系
                    # 要插入的数据
                    # 如果状态为评审中，则为pass，否则空
                    if data['status'] == 'reviewing' or data['status'] == 'closed':
                        values = (parent_id, 1, reviewer, '', None)
                    else:
                        sql = """
                            UPDATE `zt_story`
                            SET `reviewedBy` = %s, `reviewedDate` = %s
                            WHERE `id` = %s
                            """
                        values = (reviewer, current_time,parent_id)
                        pymysql_import(connection, sql, values)
                        values = (parent_id, 1, reviewer, 'pass', current_time)
                    # SQL 插入语句
                    sql = "INSERT INTO `zt_storyreview` (`story`, `version`, `reviewer`, `result`, `reviewDate`) VALUES ( %s, %s, %s, %s, %s)"
                    pymysql_import(connection, sql, values)

                    # 还需在storyspec插入描述
                    # SQL 插入语句
                    sql = "INSERT INTO `zt_storyspec` (`story`, `version`, `title`, `spec`, `files`) VALUES ( %s, %s, %s, %s, %s)"
                    # 要插入的数据
                    # 如果状态为评审中，则为pass，否则空
                    values = (parent_id, 1, data['title'], data['spec'], None)
                    pymysql_import(connection, sql, values)

                    # 如果该需求没有子节点，则需要将状态改为projected
                    if len(value['child']) == 0 or project['hasProduct'] == 0:
                        sql = """
                            UPDATE `zt_story`
                            SET `stage` = %s
                            WHERE `id` = %s
                            """
                        values = ('projected', parent_id)
                        pymysql_import(connection, sql, values)

                        # 还需在zt_projectstory插入对应的关系
                        # 如果项目为产品型项目，并且有子需求，则父需求不可以关联到项目
                        # SQL 插入语句
                        sql = "INSERT INTO `zt_projectstory` (`project`, `product`, `branch`, `story`, `version`, `order`) VALUES (%s, %s, %s, %s, %s, %s)"
                        # 要插入的数据
                        values = (project_id, product_id, 0, parent_id, 1, 0)
                        pymysql_import(connection, sql, values)

                else:
                    parent_id = value['id']

                if len(value['child']) != 0:
                # else:

                    #如果父节点的parent为0，则改为-1
                    # 修改父节点parent id
                    # 对于已有父需求，如果新增了子需求并且评审未通过，则修改状态为评审通过active，且zt_storyreview为pass
                    if value['parent'] == 0:
                        # ZtStory.objects.filter(id=parent_id).update(status='active')
                        sql = """
                            UPDATE `zt_story`
                            SET `parent` = %s
                            WHERE `id` = %s
                            """
                        values = (-1, parent_id)
                        pymysql_import(connection, sql, values)
                        # pass
                    if value['status'] != 'active':
                        # ZtStory.objects.filter(id=parent_id).update(status='active')
                        sql = """
                            UPDATE `zt_story`
                            SET `status` = %s,`reviewedDate` = %s
                            WHERE `id` = %s
                            """
                        values = ('active', current_time,parent_id)
                        pymysql_import(connection, sql, values)

                        sql = """
                            UPDATE `zt_storyreview`
                            SET `result` = %s, `reviewDate` = %s
                            WHERE `story` = %s
                            """
                        values = ('pass', current_time, parent_id)
                        pymysql_import(connection, sql, values)

                    for child_key, child_value in value['child'].items():
                        if 'add' in child_value.keys():
                            # pass
                            data, reviewer = story_data_trans(child_value['data'], parent_id, product_id)

                            # 如果状态为关闭
                            if data['status'] == 'closed':
                                closedBy = login_account
                                closedDate = current_time
                            else:
                                closedBy = ''
                                closedDate = None

                            # 插入需求
                            # url = ZENTAO_URL + 'stories'
                            # response = requests.post(url, json=data, headers=headers)
                            # story_info = json.loads(response.text)
                            # parent_id = story_info['id']
                            sql = "INSERT INTO `zt_story` " \
                                  "(`vision`, `parent`, `product`, `branch`, `module`, `plan`, `businessProjectCode`,`demandCode`," \
                                  "`detailRequirementCode`,`crossDomainCollaboration`,`impactScope`,`ownedMicroservice`,`difficulty`," \
                                  "`addStage`,`begin`,`end`,`fromBug`,`feedback`,`title`,`type`,`category`,`pri`,`estimate`,`status`," \
                                  "`stage`,`lib`,`fromStory`,`fromVersion`,`openedBy`,`openedDate`,`toBug`,`duplicateStory`,`version`," \
                                  "`storyChanged`,`demand`,`URChanged`,`deleted`,`closedBy`,`closedDate`)" \
                                  " VALUES " \
                                  "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
                                  " %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = ('rnd', parent_id, product_id, 0, data['module'], '', data['businessProjectCode'],data['demandCode'],
                                      data['detailRequirementCode'], data['crossDomainCollaboration'],data['impactScope'],
                                      data['ownedMicroservice'], data['difficulty'], data['addStage'], data['begin'],
                                      data['end'],0, 0, data['title'], 'story', data['category'], data['pri'], data['estimate'],
                                      data['status'],'projected', 0, 0, 1, login_account, current_time, 0, 0, 1, '0', 0, '0', '0',closedBy,closedDate)
                            child_id = pymysql_import(connection, sql, values)

                            # 标识为插入
                            sql = "INSERT INTO `zt_action` " \
                                  "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                                  "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                                  " VALUES " \
                                  "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = (
                                'story-import', child_id, f',{product_id},', project_id, 0, login_account, 'import',
                                current_time,
                                '', '', '0', 'rnd', 0)
                            pymysql_import(connection, sql, values)

                            # 在zt_action中标识是谁插入的
                            # SQL 插入语句
                            sql = "INSERT INTO `zt_action` " \
                                  "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                                  "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                                  " VALUES " \
                                  "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = (
                            'story', child_id, f',{product_id},', project_id, 0, login_account, 'opened', current_time,
                            '', '', '0', 'rnd', 0)
                            pymysql_import(connection, sql, values)

                            # 如果状态为激活（评审通过），则插入评审action
                            if data['status'] == 'active':
                                # 在zt_action中标识是谁插入的
                                # SQL 插入语句
                                sql = "INSERT INTO `zt_action` " \
                                      "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
                                      "`comment`,`extra`,`read`,`vision`,`efforted`)" \
                                      " VALUES " \
                                      "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                                # 要插入的数据
                                values = (
                                    'story', child_id, f',{product_id},', project_id, 0, reviewer, 'reviewed',
                                    current_time,
                                    '', 'Pass', '0', 'rnd', 0)
                                pymysql_import(connection, sql, values)

                            # django修改字段plan和status
                            # ZtStory.objects.filter(id=parent_id).update(plan=data['plan'], status=data['status'],
                            #                                             stage='projected')
                            # sql = """
                            #     UPDATE `zt_story`
                            #     SET `plan` = %s, `status` = %s, `stage` = %s
                            #     WHERE `id` = %s
                            #     """
                            # values = ('', data['status'],'projected', parent_id)
                            # pymysql_import(connection, sql, values)

                            # 还需在zt_projectstory插入对应的关系
                            # branch = story_info['branch']
                            # version = story_info['version']

                            # SQL 插入语句
                            sql = "INSERT INTO `zt_projectstory` (`project`, `product`, `branch`, `story`, `version`, `order`) VALUES (%s, %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            values = (project_id, product_id, 0, child_id, 1, 0)
                            pymysql_import(connection, sql, values)

                            # 还需在zt_storyreview插入对应的关系
                            # 要插入的数据
                            # 如果状态为评审中，则为pass，否则空
                            if data['status'] == 'reviewing' or data['status'] == 'closed':
                                values = (child_id, 1, reviewer, '', None)
                            else:
                                sql = """
                                    UPDATE `zt_story`
                                    SET `reviewedBy` = %s, `reviewedDate` = %s
                                    WHERE `id` = %s
                                    """
                                values = (reviewer, current_time, child_id)
                                pymysql_import(connection, sql, values)
                                values = (child_id, 1, reviewer, 'pass', current_time)
                            # SQL 插入语句
                            sql = "INSERT INTO `zt_storyreview` (`story`, `version`, `reviewer`, `result`, `reviewDate`) VALUES ( %s, %s, %s, %s, %s)"
                            pymysql_import(connection, sql, values)

                            #还需在storyspec插入描述
                            # SQL 插入语句
                            sql = "INSERT INTO `zt_storyspec` (`story`, `version`, `title`, `spec`, `files`) VALUES ( %s, %s, %s, %s, %s)"
                            # 要插入的数据
                            # 如果状态为评审中，则为pass，否则空
                            values = (child_id, 1, data['title'], data['spec'], None)
                            pymysql_import(connection, sql, values)

        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"数据导入报错"}, status=200)

        #关闭连接
        try:
            connection.close()
        except:
            print(traceback.format_exc())
            return JsonResponse({'code':0,'msg':"数据库连接无法关闭"}, status=200)

        res = {
            'code': 1,
            'msg': "导入成功"
        }

        return JsonResponse(res, status=200)

    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)